Quote Originally Posted by cveetan View Post
...
So imagine it like this

Row6: SUMIF($AG$5:$AM$5,"ITF",INDIRECT(CONCATENATE("AG","6:","AM","6")))

"AG" replaced by IF(33,CHAR(ROUNDDOWN((33-1)/26,0)+64)&CHAR(MOD((33-1),26)+65),CHAR(33+64))
"AM" replaced by IF(COLUMN(),CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64))
...
I've tried these as they are in various cells in a blank sheet. The first returns AG wherever it is placed, as "IF(33" is the same as saying "IF(TRUE" so it will always return AG - in that case why not replace it with "AG" ?

The second expression is incomplete - I think it should be:

=IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64))

and this will return the column identifier for whichever column it is in (you might have considered the ADDRESS function).

@Pete, I've tried your way also but couldn't make it work it gives #REF! error when calculating though CTRL + SHIFT + ENTER
I'm not really sure why you would need CSE for this, but I think your amended formula should be (taking into account what I've said above):

=SUMIF($AG$5:$AM$5,"ITF",INDIRECT("AG"&ROW(A6)&":"&IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64))&ROW(A6)))

You had your quotes in the wrong place.

Hope this helps.

Pete