Using Excel 2007;
The following formula sums every other column in a range.
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1)
How can I modify the formula to refer to a range
from column A to 'one column left of the formula'?
I've tried the following but the result is #REF!
=SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1))
A named range works well but I'll copy this formula to hundreds of lines in
multiple sheets and I'd rather not have to modify the range name for each
sheet.
Thank you.