excelexcel-formulasumifs

How to sum (SUMIF OR SUMIFS) only when there are consecutively same values?


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.


Solution

  • You don't need SUMIF for this, the formula can be defined like this:

    Assuming the above data starts at A1:

    # Cell D2
    =IF(A2<>A1,B2,B2+D1)