Sum the last 5 numbers in a column

[May 1950rc]

I have a column; some cells contain numbers and other cells are blank. I
keep adding numbers and blanks. How do I create a formula that will
automatically calculate the sum of the last 5 numbers every time I add a
number.

Thank you

 

[Domenic]

Let's assume that Column A, starting at A2, contains the data. First define
a dynamic named range...

Name: DynRange

Refers to:

=$A$2:INDEX($A:$A,MATCH(9.99999999999999E+307,$A:$A ))

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(INDEX(DynRange,LARGE(IF(DynRange<>"",ROW(DynRange)-MIN(ROW(DynRange))+1),5)):INDEX(DynRange,ROWS(DynRange)))

 

[Jarek Kujawa]

another way:

=SUM(OFFSET(A1,MAX(IF(ISNUMBER($A$1:$A$30),ROW($A$1:$A$30)))-1,,-5,1))
this is an array formula so CTRL+SHIFT+ENTER this formula instead of
simply using ENTER

adjust yr range to fit

 

[Ron Rosenfeld]

=SUM(OFFSET(A1,MAX(IF(ISNUMBER($A$1:$A$30),ROW($A$1:$A$30)))-1,,-5,1))
this is an array formula so CTRL+SHIFT+ENTER this formula instead of
simply using ENTER

adjust yr range to fit

If the OP wants to ignore the blanks, your formula does not do that.
--ron

 

[Ron Rosenfeld]

On Wed, 3 Mar 2010 16:26:01 -0800, May 1950rc <May

I have a column; some cells contain numbers and other cells are blank. I
keep adding numbers and blanks. How do I create a formula that will
automatically calculate the sum of the last 5 numbers every time I add a
number.

Thank you

I am assuming you wish to ignore the blanks.

In the formula, rng represents, for example, A1:A100. In Excel 2007 or higher,
rng can represent an entire column (e.g. A:A); but in earlier versions of
Excel, it must be less than a full column (e.g. A1:A65535).

Larger rng's will take longer to calculate, so use some reasonable size, or use
a dynamic formula to define it.

This formula must be **array-entered**:

=SUM(LOOKUP(LARGE(ISNUMBER(rng)*ROW(rng),{1,2,3,4,5}),ROW(rng),rng))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[May 1950rc]

Ron - Thank you so much. It worked great. Much appreciated.

Ron C.

 

[Jarek Kujawa]

missed that one
thks

If the OP wants to ignore the blanks, your formula does not do that.
--ron