counting Qty of parts in a list.

sammendac

New Member
Joined
May 28, 2014
Messages
37
Hi

I have approx 200 rows and 2 columns comprising of Part Numbers and Quantities.

I need to know the Quantity of each part number there is in the list.

Please help!!!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,788
ABCDE
1Part #QtyPart #Total Qty
2a11a111
3b22b27
4c33c33
5a14d47
6b25x118
7a160
8d47
9x118
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet22

Worksheet Formulas
CellFormula
E2=SUMIFS(B:B,A:A,D2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Is this what you're looking for?

If you need to create the list of unique part numbers, you can copy column A, paste it to column D, then go to the Data tab and click Remove Duplicates.


You can also use a Pivot table. Starting just with your A and B columns (nothing else yet), select those 2 columns, then on the Insert tab click Pivot Table, click OK. Now on the right side of the screen, drag Part # down to the Rows box. Drag Qty to the Values box. Now click where it says Count of Qty > Value Field Settings > Sum.


Excel has a lot of ways to do this. Hope this helps!
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
Well, how many part numbers do you have? If it's just a few, and you don't mind typing them out then you can use SUMIF:
Ex: (This range starts in A1 and ends in B21)
a 2
b 12
uioyu 18
iuy 80
iy 59
uioyu 31
uyi 52
uioyu 24
yui 93
yui 28
iyu 9
iuy 33
yui 8
yui 49
uioyu 81
uyi 41
yui 43
iuy 9
yui 67
a 26
a 86


and using this to find the sum of all occurrences of "a":
=SUMIF(A1:A21,"a",B1:B21)



If you are needing to find all unique part numbers, and then sum them, you can use this array formula:
=IF(ISERROR(INDEX($A$1:$A$21,MATCH(0,COUNTIF($I$1:I1,$A$1:$A$21),0))),"",INDEX($A$1:$A$21,MATCH(0,COUNTIF($I$1:I1,$A$1:$A$21),0)))
Where I1 is the cell directly about where you want the new list of unique part numbers, so then I2 is the beginning of the list of unique part numbers. A1:A21 is the range of all your part numbers.
Make this new list, and then use the SUMIF formula next to each part number, but replace "a" with the corresponding part number next to it.
 

TimNevins

New Member
Joined
Feb 18, 2014
Messages
39
If you have two columns with PN and QTY, create a pivot table and let it figure it out for you. It will combine any duplicate PNs and total up the QTY. Seems like the simplest solution.
 

sammendac

New Member
Joined
May 28, 2014
Messages
37
Eric

Thanks, simple and easy. I will try the pivot table option as well


Sam
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,788
Glad we could help. Let us know if you have any additional questions.
 

sammendac

New Member
Joined
May 28, 2014
Messages
37
Glad we could help. Let us know if you have any additional questions.
By using your idea and a couple of others I found, a job that could take over an hour a week, took 30 secs.

I received a few replies saying use a pivot table but why bother plus I simplified my problem, its not 2 columns, its around 20 with lots of cross referencing. Your idea was so simple

This is one of the problems of being self taught, you only learn what you need and don't know what you might need.

Thanks for your help.
 

Forum statistics

Threads
1,077,955
Messages
5,337,391
Members
399,144
Latest member
Lauren Ward

Some videos you may like

This Week's Hot Topics

Top