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