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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD]Sum first 2 Months[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


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.