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.