Sumif with only first value of each group in column

user_13756484 0
Viewed 166

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?

excel-formula excel
3 Answers

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

user_126342300

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

user_48515900

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