Forum Discussion

Copper Contributor

Mar 30, 2020

Solved

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

excel

Formulas and Functions

Abiola1
Mar 30, 2020
JimDW2450

In my example below, you can see that I am currently in cell A1. Hence, in my Create PivotTable box, the cell reference is visible..

PeterBartholomew1

Silver Contributor

Mar 30, 2020

JimDW2450

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 )