CODEWITHSUNDEEP
excelexcel-formula
Guilherme Corazza
Guilherme Corazza

Reputation: 27

Sumif with only first value of each group in column

I have a dataset similar to this, but really extensive:

Row Levels Level 1 Size Department
1 1 AA 2.0 Dept 1
2 2 AA 0.8 Dept 1
3 3 AA 1.5 Dept 1
4 2 BB 3.0 Dept 1
5 3 BB 2.0 Dept 1
6 3 BB 2.5 Dept 2
7 2 CC 5.0 Dept 2
8 3 CC 1.5 Dept 2
9 3 DD 0.5 Dept 2
10 3 DD 3.0 Dept 2
11 2 EE 4.0 Dept 2
12 3 EE 2.0 Dept 2

What I need is to achieve a total size per Department, however I want to sum only the first match per Level 1, i.e.:
Department 1 would be 2.0 (row 1) + 3.0 (row 4) = 5.0
Department 2 would be 2.5 (row 6) + 5.0 (row 7) + 0.5 (row 9) + 4.0 (row 11) = 12.0

Does anyone have any idea how to accomplish this in Excel?

Upvotes: 2

Views: 749

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

SUMIFS() will not do what you want. Use SUMPRODUCT() with some boolean:

=SUMPRODUCT($C$2:$C$13*($D$2:$D$13=F2)*(COUNTIFS(OFFSET($B$2,0,0,ROW($B$2:$B$13)-1),$B$2:$B$13,OFFSET($D$2,0,0,ROW($B$2:$B$13)-1),F2)=1))

One note, the use of OFFSET() makes this a volatile function, meaning that it will recalc with every change made to excel. If there are too many then it will slow down the responsiveness in Excel.

enter image description here

To do it without the volatility we need a helper column. In E2 put:

=COUNTIFS($D$2:D2,D2,$B$2:B2,B2)=1

And copy down. Then we can use SUMIFS():

=SUMIFS(C:C,D:D,F2,E:E,TRUE)

enter image description here

Upvotes: 3

P.b
P.b

Reputation: 11468

Alternate solution to the same formula:
=SUM(XLOOKUP(UNIQUE(FILTER(C:C,(ROW(C:C)>1)*(E:E=@$F$2#))&@$F$2#),C:C&E:E,D:D))

Where F2 holds =UNIQUE(FILTER(E:E,(ROW(E:E)>1)*(E:E<>"")))

enter image description here

Upvotes: 4

Tom Sharpe
Tom Sharpe

Reputation: 34230

If you have Excel 365, you could try something like this:

=LET(FilteredLevel,FILTER(C$2:C$13,E$2:E$13=H2),
SUM(XLOOKUP(UNIQUE(FilteredLevel),FilteredLevel,FILTER(D$2:D$13,E$2:E$13=H2))))

enter image description here

Note

You can also use full-column references if you wish

=LET(FilteredLevel,FILTER(C:C,E:E=H2),
SUM(XLOOKUP(UNIQUE(FilteredLevel),FilteredLevel,FILTER(D:D,E:E=H2))))

Upvotes: 3

Related Questions