Google-sheets – Sum upwards until another column’s value is met

google sheets

I found a handful solutions for summing down, but nothing for summing up.

Example table:

type | value
A    | -
B    | 5
C    | 10
D    | 7
A    | [SUM1 = 7+10+5 = 22]
B    | 4
B    | 2
C    | 3
C    | 5
A    | [SUM2 = 5+3+2+4 = 14]

Goal: Each SUM cell (type A) is summing upwards until the next A is seen in the "type" column.

You can open and edit this example as Google Sheet here.

I have tried MATCH, VLOOKUP, INDEX and others without success.

=MATCH("A";A2:A11;0) would give me always the position of the first "A" from top.

=INDEX(B2:B11;MATCH("A";A2:A11;0);0) would give me the value, not the cell index.

A similiar question Sum the value of the cells above going up until blank cell only shows how to do this by hand.

Another similar question does not answer the case above: Getting a sum of cells (bottom to top) in Google Sheets until a specific value is matched

This post on Google Groups proposes something I don't grasp:

=if(trim(A9)="TOTALS:",sum(arrayformula(index(B8):index(B:B,min(arrayformula(if(isnumber(B1:B8),row(B1:B8))))))),iferror(1/0))

Best Answer

Paste the following formula into all type "A" columns:

=subtotal(9,indirect("b"&ArrayFormula((match(2,1/(indirect("A1:A"&row()-1)="A"))))&":b"&row()-1))

You might need to change the b & a's on the formula if your data is on another column. The solution is also on your Google Sheets.

Related Topic