Thread: sum next two non-empty cells in a range

is there a way to tell excel to sum the next two non-empty cells in a range?

A1 = 5
A2 = ""
A3 = ""
A4 = 4
A5 = ""

A1 = 5
A2 = 4
A3 = ""
A4 = 7
A5 = ""

if i wanted to evaluate A1:A5, and add only the first two non-empty cells,
so either way, the formula would return the result of 9. TIA.

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(SUBTOTAL(9,OFFSET(A1,SMALL(IF(A1:A5<>"",ROW(A1:A5)-CELL("row",A1)),{
1,2}),0)))

Hope this helps!

In article <YYgEd.14656$dt3.591174@twister.southeast.rr.com>,
"Spencer Hutton" <shutton1@carolina.rr.com> wrote:

> is there a way to tell excel to sum the next two non-empty cells in a range?
>
> A1 = 5
> A2 = ""
> A3 = ""
> A4 = 4
> A5 = ""
>
> A1 = 5
> A2 = 4
> A3 = ""
> A4 = 7
> A5 = ""
>
> if i wanted to evaluate A1:A5, and add only the first two non-empty cells,
> so either way, the formula would return the result of 9. TIA.

Just another option to try:

Assuming source range is in col A, A1 down

Put in say, B1:

=SUM(OFFSET($A$1,,,SMALL(IF(A1:A10<>"",ROW(A1:A10)),{2})))

Array-enter the formula
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Adapt to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Spencer Hutton" <shutton1@carolina.rr.com> wrote in message
news:YYgEd.14656$dt3.591174@twister.southeast.rr.com...
> is there a way to tell excel to sum the next two non-empty cells in a
range?
>
> A1 = 5
> A2 = ""
> A3 = ""
> A4 = 4
> A5 = ""
>
> A1 = 5
> A2 = 4
> A3 = ""
> A4 = 7
> A5 = ""
>
> if i wanted to evaluate A1:A5, and add only the first two non-empty cells,
> so either way, the formula would return the result of 9. TIA.
>
>

=SUM(OFFSET(A1,,,SMALL(IF(A1:A5<>"",ROW(A1:A5)),2)))

Array-entered.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>is there a way to tell excel to sum the next two non-
empty cells in a range?
>
>A1 = 5
>A2 = ""
>A3 = ""
>A4 = 4
>A5 = ""
>
>A1 = 5
>A2 = 4
>A3 = ""
>A4 = 7
>A5 = ""
>
>if i wanted to evaluate A1:A5, and add only the first two
non-empty cells,
>so either way, the formula would return the result of
9. TIA.
>
>
>.
>