specify an ending row in an array formula
 

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.

specify an ending row in an array formula
 

Try

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

"Bassman62" wrote:

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.

specify an ending row in an array formula
 

Thank you!
Works wonderfully.

"Sheeloo" wrote:

Try

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

"Bassman62" wrote:

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.

specify an ending row in an array formula
 

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

"Bassman62" wrote in message
...
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.