sum next two non-empty cells in a range

[Spencer Hutton]

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.

 

[Domenic]

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!

 

[Max]

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

 

[Jason Morin]

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

Array-entered.

HTH
Jason
Atlanta, GA