Formula to find items in Excel and determine total quantity of each item

Question

I have a steel cut list in Excel. There are over 1000 rows of information and probably 100 different part numbers. Is there a formula that can find the total quantity for each part number?

I am hoping not to have to go through the entire list and sum up 100 different items.

Here is a sample of 4 rows of my Excel spreadsheet:

(row)  MARK DESCRIPTION     LENGTH        ANGLE1    ANGLE2  QTY.
1      A1   TR3x1.5x0.1875  3'-1 3/8"     0         0       2
2      A2   TR3x1.5x0.1875  3'-2 7/8"     0         0       4
3      A2   TR3x1.5x0.1875  3'-2 7/8"     0         0       5
4      A3   TR3x1.5x0.1875  1'-6 11/16"   0         0       1

Answer 1

This is an easy, two step solution.

1. Convert your data into an Excel Table (this may not matter much now, but it provides a ton of long-term flexibility in managing your data).
2. Convert your Excel Table into a Pivot Table with:
   • Row Labels = Mark (assuming this is your part number
   • Values = Quantity (and make sure that your Pivot table is summing (not counting) this value).