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 |
CodePudding user response：
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))))
CodePudding user response：
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) )))