sum next two non-empty cells in a range

  • Thread starter Thread starter Spencer Hutton
  • Start date Start date
S

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.
 
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!
 
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
 
=SUM(OFFSET(A1,,,SMALL(IF(A1:A5<>"",ROW(A1:A5)),2)))

Array-entered.

HTH
Jason
Atlanta, GA
 
Back
Top