Sum alternate columns with dynamic range

[nanjundas]

I have a excel sheet having monthwise sales (qty and value) in column against each item in row. I need to sum the alternate column (i.e qty and value seperately). I am using the formula "=SUMPRODUCT(--(MOD(COLUMN(D4:AA4)-COLUMN(D4)+1,2)=0),D4:AA4 " which gives total of all the qty and value individually.
I need to make the range D4:AA4 as dynamic based on the cell values. (if i want to refer to Q4 or S4 in place of AA4)

Can anyone help me out.

 

[James006]

Hello,

You should consider using the Indirect() function ... :wink:

See Debra's excellent explanation : http://www.contextures.com/xlFunctions05.html

HTH

 

[nanjundas]

Hello,

You should consider using the Indirect() function ... :wink:

See Debra's excellent explanation : http://www.contextures.com/xlFunctions05.html

HTH

Hi James,

Thanks for the reply. If the row is fixed the below formula works. When i tried to make the row dynamic it is #value ! error. Can you help me out.
=INDIRECT(CONCATENATE("D",ROW(),":",CELL("address",OFFSET(D5,0,17))))

 

[Aladin Akyurek]

In B4 enter and copy down:

=MATCH(9.99999999999999E+307,D4:XFD4)

In C4 enter and copy down:

=SUMPRODUCT(--(MOD(COLUMN(D4:INDEX(D4:XFD,B4))-COLUMN(D4)+1,2)=0),D4:INDEX(D4:XFD,B4)