Sum last three numbers in a column

[beginner john]

This formula results in the last nimber in a column
=index(k5:k57,count(k5:k57))
How do I get the last three numbers in a column?

 

[Jacob Skaria]

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=SUM(LOOKUP(LARGE(IF(ISNUMBER(K5:K57),ROW(K5:K57)),{1,2,3}),ROW(K5:K57),K5:K57))

If this post helps click Yes

 

[T. Valko]

Re: Sum last three numbers in a column

Will there *always* be at least 3 numbers to sum? If, not then what should
happen?

Are there any empty/blank cells within the range?

 

[beginner john]

Re: Sum last three numbers in a column

Will there *always* be at least 3 numbers to sum? If, not then what should
happen?

Are there any empty/blank cells within the range?

--
Biff
Microsoft Excel MVP





There will always be three and no blank cells.

 

[T. Valko]

There will always be three and no blank cells.

Try one of these:

=SUM(INDEX(K5:K57,COUNT(K5:K27)-2):INDEX(K5:K57,COUNT(K5:K57)))

=SUM(OFFSET(K5,COUNT(K5:K57)-1,,-3))

That short formula is nice and compact but it uses the volatile function
OFFSET which will calculate *every time* a calculation is triggered.
Sometimes this is not desireable. Try it out, see what you think!

 

[Mike H]

Hi

Try this

=SUM(OFFSET(K1,MAX(0,COUNTA(K1:K57)-3),0,3,1))

Mike

 

[Mike H]

oops,

you want to start in k5

=SUM(OFFSET(K5,MAX(0,COUNTA(K5:K57)-3),0,3,1))

Mike

 

[Chip Pearson]

You subject line says "Sum" but your question seems to just to get the
values. For the sum of the last three numbers in K5:K57, use the
following array formula:

=SUM(OFFSET($K$5,MAX(ROW($K$5:$K$57)*($K$5:$K$57<>""))-ROW($K$5)-2,0,3,1))

If you want the three actual values in three separate cells, select
the three (contiguous) cells in which you want the values to appear,
enter one of formulas below, and press CTRL SHIFT ENTER

If your selected result cells are in one column spanning several rows,
use the following:
=INDEX($K$5:$K$57,LARGE(ROW($K$5:$K$57)*($K$5:$K$57<>""),{3;2;1})-ROW($K$5)+1,1)


If your selected result cells are in one row spanning several columns,
use the following formula:
=INDEX($K$5:$K$57,LARGE(ROW($K$5:$K$57)*($K$5:$K$57<>""),{3,2,1})-ROW($K$5)+1,1)

The only difference between these formulas is that the values in the
array {3,2,1} are separated by semi-colons in the first formula and by
commas the second formula.

Note that all of these formulas are array formulas.
Since these are Array Formulas, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 7 Sep 2009 10:05:02 -0700, beginner john <beginner