Hi Jacob
For the formula bar is that F2 followed by CTRL+SHIFT+ENTER?

Also, am I correct is assuming that this formula seeks to sum the last 21
rows in rows A1:A100 that have a value in them - ie if rows80-100 all have
values then it would sum them all. If say row 99 had no value in it, then it
would sum rows 79:100, being 21 cells with a value?

If I wanted to do this in a column, would I simply replace the word Row in
the formula with column?

ta


"Jacob Skaria" wrote:

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

'sum of last 5 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

'sum of last 21 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:21)),ROW(A1:A100),A1:A100))


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

Hi

I am after the formula that will allow me to sum the values of the last 21
cells in a column that have a value. As some cells are blank, at times the
formula will have to count back more than 21 cells, sometimes only 21.

Is there a way other than manully changing the sum formula to increase the
range of cells covered whenever a blank cell occurs?

Thanks