Sum last 3 values
 

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

One way

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

entered nwith ctrl + shift & enter

--

Regards,

Peo Sjoblom


"Lisa" wrote in message
...
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

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


"Lisa" wrote in
message
...
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



.

On Tue, 1 Mar 2005 10:34:24 -0800, "Lisa"
wrote:

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