CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
JOY
JOY

Reputation: 389

SUM a range based on the last cell on Google Sheets

I have a table as below. Now I'd like to SUM all the values from B1 to the cell which matches with the value of column A that I input. Ex: If I input c, it will SUM from B1 to B3.

A B
1 a 2
2 b 4
3 c 7
4 d 1
5 h 5
6 z 3

Upvotes: 0

Views: 221

Answers (2)

player0
player0

Reputation: 1

if C1 = c try in D1:

=INDEX(SUM(INDIRECT(
 ADDRESS(MATCH(C1, A:A, 0), 2)&":"&
 ADDRESS(MAX(FILTER(ROW(A:A), A:A=C1)), 2) )))

update:

=INDEX(SUM(INDIRECT(
 ADDRESS(1, 2)&":"&
 ADDRESS(MAX(FILTER(ROW(A:A), A:A=C1)), 2) )))

enter image description here

Upvotes: 1

basic
basic

Reputation: 11968

If values in A are unique or you want sum to first found value it is enought:

=SUM(INDEX(B:B,1):INDEX(B:B,MATCH(C1,A:A,0)))

or if not unique and you want sum to last value:

=SUM(INDEX(B:B,1):INDEX(B:B,LOOKUP(99^99,ROW(A:A)/--(A:A=C1))))

enter image description here

Upvotes: 2

Related Questions