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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top