I have a condition column where some “condition values” are consecutive, which may or may not repeat further down, while some are single “condition values”. I need to sum (either using SUMIF or SUMIFS) only those values whose “condition values” are consecutive. For example,
| Condition | Values | Sum |
|---|---|---|
| A | 20 | 20 |
| A | 25 | 45 |
| B | 10 | 10 |
| B | 15 | 25 |
| B | 22 | 47 |
| C | 12 | 12 |
| D | 30 | 30 |
| D | 25 | 55 |
| D | 24 | 79 |
| A | 24 | 24 |
| A | 18 | 42 |
| A | 26 | 68 |
| D | 20 | 20 |
| D | 20 | 40 |
| B | 25 | 25 |
| B | 25 | 50 |
| B | 20 | 70 |
| C | 18 | 18 |
| C | 12 | 30 |
Note the sum of A’s after D’s starts with 0 and adds only the three consecutive A’s.
Use a simple IF condition to check if column A matches the row above.
=IF(A2=A1,C1+B2,B2)
You don’t need SUMIF for this, the formula can be defined like this:
- If Condition on current row != Condition on the previous row, Sum for this row = Value for this row
- Else, Sum for this row = Sum for previous row + Value for this row. This will take care of the “consecutive” aspect.
Assuming the above data starts at A1:
# Cell D2
=IF(A2<>A1,B2,B2+D1)
A Running Sum
- In MS365, you could do the following:
=LET(cond,A2:A20,vals,B2:B20,
SCAN(0,MAP(vals,cond=VSTACK("",DROP(cond,-1)),
LAMBDA(v,c,LAMBDA(x,v+x*c))),
LAMBDA(x,f,f(x))))
-
How this works is covered in DjC’s answer to the similar question Restarting and resuming a running total for multiple values.
-
The corresponding running count can also be produced using the
SCANfunction in the following way:
=LET(cond,A2:A20,
SCAN(0,VSTACK("",DROP(cond,-1))=cond,
LAMBDA(rr,r,IF(r,rr+1,1))))