Sumifs and Unique Entries

[rex759]

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
	A	B	C	D	E	F	G	H
1	MASTER LISTING			SUMIFS		RESULTS
2	Location	Model#	Qty			Location	Model#	Qty
3	G117-X01-06	62238391	10	25		G117-X01-06	62238391	25
4	G117-X01-06	62238391	15			G117-X01-06	62238392	20
5	G117-X01-06	62238392	12	20		G118-S02-06	62238391	136
6	G117-X01-06	62238392	8			G118-V02-06	62238391	243
7	G118-S02-06	62238391	22	136		G119-V01-06	62238391	4407
8	G118-S02-06	62238391	55			G119-W02-06	62238391	5464
9	G118-S02-06	62238391	59			G119-W02-02	62238391	915
10	G118-V02-06	62238391	37	243		G119-Y01-06	62238391	3565
11	G118-V02-06	62238391	119			G119-V01-06	62238377	1564
12	G118-V02-06	62238391	87
13	G119-V01-06	62238391	99	4407
14	G119-V01-06	62238391	65
15	G119-V01-06	62238377	1564	1564
16	G119-V01-06	62238391	351
17	G119-V01-06	62238391	3541
18	G119-V01-06	62238391	351
19	G119-W02-06	62238391	5464	5464
20	G119-W02-02	62238391	915	915
21	G119-Y01-06	62238391	3565	3565
Sheet1

Cell Formulas
Range	Formula
D3,D19:D21,D15,D13,D10,D7,D5	D3	=SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	MASTER LISTING			SUMIFS		RESULTS
2	Location	Model#	Qty			Location	Model#	Qty
3	G117-X01-06	62238391	10	25		G117-X01-06	62238391	25
4	G117-X01-06	62238391	15			G117-X01-06	62238392	20
5	G117-X01-06	62238392	12	20		G118-S02-06	62238391	136
6	G117-X01-06	62238392	8			G118-V02-06	62238391	243
7	G118-S02-06	62238391	22	136		G119-V01-06	62238391	4407
8	G118-S02-06	62238391	55			G119-V01-06	62238377	1564
9	G118-S02-06	62238391	59			G119-W02-06	62238391	5464
10	G118-V02-06	62238391	37	243		G119-W02-02	62238391	915
11	G118-V02-06	62238391	119			G119-Y01-06	62238391	3565
12	G118-V02-06	62238391	87
13	G119-V01-06	62238391	99	4407
14	G119-V01-06	62238391	65
15	G119-V01-06	62238377	1564	1564
16	G119-V01-06	62238391	351
17	G119-V01-06	62238391	3541
18	G119-V01-06	62238391	351
19	G119-W02-06	62238391	5464	5464
20	G119-W02-02	62238391	915	915
21	G119-Y01-06	62238391	3565	3565
22
Master

Cell Formulas
Range	Formula
D3,D19:D21,D15,D13,D10,D7,D5	D3	=SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3)
F3:H11	F3	=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.

 

[Joe4]

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

=IF(A3&B3<>A2&B2,SUMIFS($C$3:$C$21,$A$3:$A$21,A3,$B$3:$B$21,B3),"")

 

[rex759]

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.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

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:

=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),"")

 

[rex759]

Very nice! Both options work perfectly. Thank you again.