Excel formula to sum value in column to the left in array formula based on criteria

Question

I have a row that looks like this: |52|52|52|_|_|_|_|_|13|B|_|_|_|_|_|24|C|_|_|_|_|_|18|B|_|_|_|_|_|16|A|

The vertical pipes represent columns and the underscores represent blank cells.

I want to sum the numbers in the cells before all "B" value cells (i.e. 13 & 18 in the example)

I can't change the row or add helper columns, so I'm limited in that regards. I thought I could do this with an array formula, but it's not working as I expected.

My array formula is currently:
{=SUM(IF(Table1[#Totals]="B*",INDEX(Table1[#Totals],1,COLUMN(Table1[#Totals])-1),0))}

I also tried:
{=SUM(IF(Table1[#Totals]="B*",OFFSET(Table1[#Totals],0,-1),0))}

The offset version just returned an array full of !REF errors.

The current INDEX formula almost works - when I step through it I see it generates an array full of "TRUE" and "FALSE" matches for the IF criteria, and it generates an array full of "0"s and letter "B"s for the INDEX portion, however, I need the value in the cell BEFORE the letter "B", not the letter "B" itself, and I'm a little lost on why the COLUMN(Table1[#Totals])-1 portion doesn't give me that.

As I step through the formula, I see it's evaluating the COLUMN(Table1[#Totals])-1 step as simply 1-1=0 and so it's offsetting 0 cells, which is not what I expected.

Any help getting the -1 column offset to work would be much appreciated, as I'm pretty stumped now.

Answer 1

Assumption:

• Your sample data sits in range A1:AE1
• Numbers in your sample data are actually true numbers, not text.

You can use:

=SUMPRODUCT(--(B1:AE1="B"),A1:AD1)

You should be able to use these two different ranges in SUMPRODUCT since your "B" values would always be one cell to the right of the second range. Change ranges according to your data.

Also note, OFFSET is volatile and would recalculate upon any sheet recalculation, save and even opening the workbook.

Answer 2

After playing around with it a bit, I figured out the offest function was throwing an error because the table started in column "A" (the first column) so the result of the -1 column offset was a REF! error.

I got this working using the offset function just by inserting a blank column before the table (so shifting the table over so it starts in column B).

However, JvdV provided an answer which doesn't use offset, which I think is a better solution overall, so I've accepted that as the answer.