Keeping number of rows in Excel formula

July 3, 2009 at 12:46:03
Specs: Windows XP
If I have a formula in A1 that reads: =sum(a2:a5000) to get the sum of up to 5000 rows. Is it possible to have that value remains as 5000 after I insert or delete some rows? TIA.

See More: Keeping number of rows in Excel formula

Report •


#1
July 3, 2009 at 13:59:47
You can use the =INDIRECT function:

In Cell B1 enter A5000

In Cell A1 enter =SUM(A2:INDIRECT(B1))

MIKE

http://www.skeptic.com/


Report •

#2
July 3, 2009 at 15:46:14
No need to "waste" B1.

=SUM(A2:INDIRECT("A5000"))

Keep in mind that in either case, you can't mess with Row 2. You will get a #REF error if you delete Row 2 or the Formula will change to =SUM(A3:INDIRECT("A5000")) if you insert a Row at 2.

If you want to lock in both the upper and lower cell, use this:

=SUM(INDIRECT("A2"):INDIRECT("A5000"))


Report •

#3
July 6, 2009 at 12:56:59
Thank you for both of your input. I didn't know what INDIRECT was all about, and now I do…Thanks again.

Report •

Related Solutions


Ask Question