Sum last 3 values

[Lisa]

Good morning

I need to sum the last 3 values in a range that can
contain empty cells.

A1 2.2
A2
A3 4.3
A4 1.7
A5 2.2
A6
A7 3.4
A8
A9 1.0
A10

So I need to sum 1.0, 3.4 and 2.2. The closest I've come
is a formula that sums the row numbers themselves: 9, 7, 5
and not the values in the cells.

TIA
Lisa

 

[Peo Sjoblom]

One way

=SUM(A1000:INDEX(A:A,LARGE(IF(A2:A1000<>"",ROW(A2:A1000)),3)))

entered nwith ctrl + shift & enter

 

[Lisa]

Thank you! I was very close.

-----Original Message-----
One way

=SUM(A1000:INDEX(A:A,LARGE(IF(A2:A1000<>"",ROW (A2:A1000)),3)))

entered nwith ctrl + shift & enter

--

Regards,

Peo Sjoblom





.

 

[Ron Rosenfeld]

Good morning

I need to sum the last 3 values in a range that can
contain empty cells.

A1 2.2
A2
A3 4.3
A4 1.7
A5 2.2
A6
A7 3.4
A8
A9 1.0
A10

So I need to sum 1.0, 3.4 and 2.2. The closest I've come
is a formula that sums the row numbers themselves: 9, 7, 5
and not the values in the cells.

TIA
Lisa

If your range of numbers is in, let us say A1:A100, then the following *array*
formula will do what you describe:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(
LARGE(ISNUMBER(A1:A100)*ROW(
INDIRECT("1:100")),{1,2,3}),1))))

To enter an *array* formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>. XL will place braces {...} around
the formula.


--ron