Sumifs and Unique Entries

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
610
Office Version
  1. 365
Platform
  1. Windows
Hello,
My goal is to combine quantities in column C when there are multiple duplicate entries of Locations and Model numbers
Example, G117-X01-06 has the model# 62238391 listed twice. I want to total the quantities into one entry

I thought I could create a unique list of locations, but that won’t work because a location could have up to 20 different model numbers.

I thought sumifs would be a start but would have to add a condition NOT sum if it already happened for the same duplicate location and model numbers.

I can add any helper columns, just not sure what direction to take.

Any advice/help is appreciated.

Book1.xlsx
ABCDEFGH
1MASTER LISTINGSUMIFSRESULTS
2LocationModel#QtyLocationModel#Qty
3G117-X01-06622383911025G117-X01-066223839125
4G117-X01-066223839115G117-X01-066223839220
5G117-X01-06622383921220G118-S02-0662238391136
6G117-X01-06622383928G118-V02-0662238391243
7G118-S02-066223839122136G119-V01-06622383914407
8G118-S02-066223839155G119-W02-06622383915464
9G118-S02-066223839159G119-W02-0262238391915
10G118-V02-066223839137243G119-Y01-06622383913565
11G118-V02-0662238391119G119-V01-06622383771564
12G118-V02-066223839187
13G119-V01-0662238391994407
14G119-V01-066223839165
15G119-V01-066223837715641564
16G119-V01-0662238391351
17G119-V01-06622383913541
18G119-V01-0662238391351
19G119-W02-066223839154645464
20G119-W02-0262238391915915
21G119-Y01-066223839135653565
Sheet1
Cell Formulas
RangeFormula
D3,D19:D21,D15,D13,D10,D7,D5D3=SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
How about
Fluff.xlsm
ABCDEFGH
1MASTER LISTINGSUMIFSRESULTS
2LocationModel#QtyLocationModel#Qty
3G117-X01-06622383911025G117-X01-066223839125
4G117-X01-066223839115G117-X01-066223839220
5G117-X01-06622383921220G118-S02-0662238391136
6G117-X01-06622383928G118-V02-0662238391243
7G118-S02-066223839122136G119-V01-06622383914407
8G118-S02-066223839155G119-V01-06622383771564
9G118-S02-066223839159G119-W02-06622383915464
10G118-V02-066223839137243G119-W02-0262238391915
11G118-V02-0662238391119G119-Y01-06622383913565
12G118-V02-066223839187
13G119-V01-0662238391994407
14G119-V01-066223839165
15G119-V01-066223837715641564
16G119-V01-0662238391351
17G119-V01-06622383913541
18G119-V01-0662238391351
19G119-W02-066223839154645464
20G119-W02-0262238391915915
21G119-Y01-066223839135653565
22
Master
Cell Formulas
RangeFormula
D3,D19:D21,D15,D13,D10,D7,D5D3=SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3)
F3:H11F3=LET(u,UNIQUE(FILTER(A3:B100,A3:A100<>"")),HSTACK(u,BYROW(u,LAMBDA(br,SUM(FILTER(C3:C100,(A3:A100=INDEX(br,,1))*(B3:B100=INDEX(br,,2))))))))
Dynamic array formulas.
 
Upvote 1
Solution
Note: Looks like Fluff gave you a solution for your RESULTS, but if you also want your formulas in cell D3 to only show a total in the first instance of a Location/Model combination, you can update your current formula in column C to this (enter in C3 and copy down):
Excel Formula:
=IF(A3&B3<>A2&B2,SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3),"")
 
Upvote 0
Thank you both.

Joe - your formula produced a duplicate total for rows 13 & 16. In this instance, the model number and location repeat itself further down.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Thank you both.

Joe - your formula produced a duplicate total for rows 13 & 16. In this instance, the model number and location repeat itself further down.
Sorry, I missed that they weren't already sorted. This update should handle that:
Excel Formula:
=IF(COUNTIFS($A$3:$A3,A3,$B$3:$B3,B3)=1,SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3),"")
 
Upvote 0
Very nice! Both options work perfectly. Thank you again.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top