specify an ending row in an array formula

[Bassman62]

I use the following formula to sum every other row up to row 500.
=SUMPRODUCT((MOD(ROW(A1:A500),2)=0)*A1:A500)
However, periodically rows within that range are inserted or deleted.
How can make the formula dynamic to specify the last row of the array to be
the row just above the formula.
Example:
If the formula is in row 600 then SUMPRODUCT...A1:A599
but if the fomula ends up in row 550 then SUMPRODUCT...A1:A549
Thank you.

 

[Sheeloo]

Try

=SUMPRODUCT((MOD(ROW(A$1:INDIRECT("A"&ROW()-1)),2)=0)*A$1:INDIRECT("A"&ROW()-1))

 

[Bassman62]

Thank you!
Works wonderfully.

Try

=SUMPRODUCT((MOD(ROW(A$1:INDIRECT("A"&ROW()-1)),2)=0)*A$1:INDIRECT("A"&ROW()-1))

 

[Ashish Mathur]

Hi,

You can simply convert the range to a list. Doing so will expand the range
automatically as and when you add more data in rows

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com