CODEWITHSUNDEEP
excelexcel-formulasumifs
user3748090
user3748090

Reputation: 15

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.

Upvotes: 1

Views: 75

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

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

=LET(cond,A2:A20,
    SCAN(0,VSTACK("",DROP(cond,-1))=cond,
        LAMBDA(rr,r,IF(r,rr+1,1))))

enter image description here

Upvotes: 1

liver
liver

Reputation: 191

Use a simple IF condition to check if column A matches the row above.

=IF(A2=A1,C1+B2,B2)

enter image description here

Upvotes: 2

Code Different
Code Different

Reputation: 93161

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)

Upvotes: 1

Related Questions