Forum Discussion
Count Numbers and Quantities
I have Column A that has multiple duplicate part numbers Column B shows a QTY for that part number
I want to remove duplicates but count the quantities, so for example
Part Number QTY
A B
123 3
123 3
456 5
456 2
Result
Part Number Total QTY
123 6
456 7
Can anyone tell me how to do that?
10 Replies
The difficult part is to get a list on distinct part numbers.
In traditional Excel that can be most easily done manually. Formulas involving MATCH, SMALL and INDEX are possible, but laborious. In the most recent versions of Office365, the formula is
= UNIQUE( Parts[Number] )
where I have assumed the basic data is held in a Table named 'Parts';
[requires Office2007 or later].
Either way the count is returned using SUMIFS
= SUMIFS( Parts[QTY], Parts[Number], distinctPart )
- Hello, you can use PivotTable to do that easily
To use the PivotTable:
1. Click inside the data
2. In the Insert tab, on the far left, click on PivotTable
3. In the PivotTable dialogue box, click on OK
In the PivotTable Fields, drag and drop Part Number inside Rows and Qty inside Values
That is all
If this answers your question, kindly accept as Best Response
