Setting up a dynamically generated range reference

Hi everyone,


I am trying to adjust a dynamically generated range reference formula to integrate later in a SUMIF function.

INDIRECT(CONCATENATE("AG","6:","AM","6"), where

*"AG" & "AM" are dynamically generated column references and
6 is a static row number that I want to make dynamic as well by replacing it with ROW() function as follows:

INDIRECT(CONCATENATE("AG","6:","AM",ROW())

The problem comes with the first number (6). It gives an error when trying to replace it with the ROW() function. Removing the quotes around it also doesn't help as it seems to break the consistency of the formula and it can't work without it.

The thing is that I want to copy the SUMIF formula down and the row number to change according to the Row where the cell is situated.

Row6: SUMIF($AG$5:$AM$5,"ITF",INDIRECT(CONCATENATE("AG","6:","AM","6")))
Row7: SUMIF($AG$5:$AM$5,"ITF",INDIRECT(CONCATENATE("AG","7:","AM","7")))
Row8: SUMIF($AG$5:$AM$5,"ITF",INDIRECT(CONCATENATE("AG","8:","AM","8")))

Copy paste doesn't work, so I am looking for a workaround solution to make the static row number dynamic.
Any suggestions outside the VBA functions will be appreciated. Maybe an alternative formula?

Thanks,

Cvetan