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))

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.