Thanks Jacob, that's brilliant!

Cheers

"Jacob Skaria" wrote:

Should have been...
=SUM(LOOKUP(LARGE(IF(ISNUMBER(AI11:AI271),ROW(AI11 :AI271)),ROW(1:5)),ROW(AI11:AI271),AI11:AI271))

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


"David" wrote:

Hi Jacob
Can't seem to get this to work

Below is the formula with the actuial cell references in it that I am trying
to calualte, in case the error is with my transcribing (the column is Column
AI, the relevant rows 11 to 271, not all of which cells have numeric entries
). I am after the sum of the last 21 cells from row 271 back that have a
numeric value.

=SUM(LOOKUP(LARGE(IF(ISNUMBER(AI11:AI271),COLUMN(A I11:AI271)),ROW(1:5)),COLUMN(AI11:AI271),AI11:AI27 1))

It provides a value but it is incorrect?



"Jacob Skaria" wrote:

Hi David

Press F2. Copy formula to the cell. Instead of enter Ctrl+Shift+Enter

'to sum last 5 numbers in the first row
=SUM(LOOKUP(LARGE(IF(ISNUMBER(1:1),COLUMN(1:1)),RO W(1:5)),COLUMN(1:1),1:1))

'to sum last 5 numbers in the range A1:J1
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:J1),COLUMN(A1:J1) ),ROW(1:5)),COLUMN(A1:J1),A1:J1))



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


"David" wrote:

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