Returning first two values in row, including 0

[BiggusDoggus]

Hi all

So I have a table of data, with the columns being the months of the year. Each row has a range of data, with some or all months having values entered.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Sales

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Sum first 2 Months

1

5

6

8

6

3

4

7

9

7

<tbody>
</tbody>

What I'm wanting to be returned in column O is the sum of the values in the first two cells of each row, from the first non-blank cell. That sum needs to include if the second cell from the first non-blank is in fact blank.


The current formula in column O is

{=IFERROR(IF(SUM(B2:INDEX(B2:M2,SMALL(IF(ISNUMBER(B2:M2),COLUMN(B2:M2)-COLUMN(B2)+1),MIN(2,COUNT(B2:M2)))))>200,"",SUM(B2:INDEX(B2:M2,SMALL(IF(ISNUMBER(B2:M2),COLUMN(B2:M2)-COLUMN(B2)+1),MIN(2,COUNT(B2:M2)))))),"")}

As you can see, row 2 works fine - the first 2 values are 1 in Feb and 5 in March, so the sum is 6.
However, row 3 is not returning what I want it to. The first non-blank cell has a value of 3, and the next cell is blank, so I want it to return 3 as the sum.

What it's doing is ignoring any subsequent blank cells after the first non blank.

I didn't write the existing formula (it's far too complex for me to have written with my current relatively limited Excel knowledge) so I don't know how to amend it.

Any help would be greatly appreciated please!

 

[Yongle]

Try this array formula in cell O2 copied down

=INDEX(B2:M2, MATCH(FALSE, ISBLANK(B2:M2), 0))+INDEX(B2:M2, MATCH(FALSE, ISBLANK(B2:M2), 0)+1)

Confirm an array formula with {Ctrl}{Shift}{Enter}

 

[BiggusDoggus]

Awesome, Yongle, thanks! Much more concise, too.

 

[Peter_SSs]

Much more concise, too.

.. or more so ..
{=SUM(B2:INDEX(B2:M2, MATCH(FALSE, B2:M2="", 0)+1))}

.. or not so concise, but not requiring the Ctrl+Shift+Enter
=SUM(B2:INDEX(B2:M2,AGGREGATE(15,6,(COLUMN(B2:M2)-COLUMN(B2)+1)/(B2:M2<>""),1)+1))

One further comment, that apparently isn't relevant to the OP but may be to other readers: The post 2 formula requires the blank cells to be truly blank and not filled by formula returning "". The formulas in this post do not.

 

[Yongle]

One further comment, that apparently isn't relevant to the OP but may be to other readers: The post 2 formula requires the blank cells to be truly blank and not filled by formula returning "". The formulas in this post do not.

and the post 2 equivalent...

=INDEX(B2:M2, MATCH(FALSE, B2:M2="", 0))+INDEX(B2:M2, MATCH(FALSE, B2:M2="", 0)+1)

 

[forginganewone]

{=SUM(B2:INDEX(B2:M2, MATCH(FALSE, B2:M2="", 0)+1))}

Why do we convert a formula into array by pressing ctrl shift entr.
What changes between it being a simple formula and being an array formula

 

[Yongle]

@forginganewone
array formulas are extemely powerful
for an explanation, search for Excel What is an array formula on google

For starters have a look at Microsoft guidance to array formulas

 

[Peter_SSs]

{=SUM(B2:INDEX(B2:M2, MATCH(FALSE, B2:M2="", 0)+1))}

Why do we convert a formula into array by pressing ctrl shift entr.

So that it works?

What changes between it being a simple formula and being an array formula

Without the array entry the blue part returns an error & therefore so does the whole formula. With the array entry the blue part compares each indiviual cell in the range to "" and returns an array of 12 TRUE/FALSE results which is used by the MATCH function.