Counting question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Column 1 contains a product ID and could contain just one entry or several
entries depending upon how many reels of that product ID we have in
inventory. Column 2 has the reel quantity in feet. I need to get a total
quantity in feet for each product ID.

Column 1 Column 2
Prod ID Feet
ABC 100
ABC 150
ABC 200
XYZ 50
XYZ 50

I need the total product, in this example ABC = 450 ft and XYZ = 100 ft. I
have over 3000 lines and over 600 products I can't do it manually. I think a
pivot table would do it but I'm not sure...

tks,
steve
 
To use sumif, you have create 600 sumif forumulaes, which is tedious.

A quick one: Sort on column 1 and do a subtotal (choosing Data -> Subtotal
from the menu bar). Collapse the detail and you have 600 lines subtotal lines
to print.
 
sjs said:
Hello,

Column 1 contains a product ID and could contain just one entry or several
entries depending upon how many reels of that product ID we have in
inventory. Column 2 has the reel quantity in feet. I need to get a total
quantity in feet for each product ID.

Column 1 Column 2
Prod ID Feet
ABC 100
ABC 150
ABC 200
XYZ 50
XYZ 50

I need the total product, in this example ABC = 450 ft and XYZ = 100 ft. I
have over 3000 lines and over 600 products I can't do it manually. I think a
pivot table would do it but I'm not sure...

tks,
steve
Click to expand...
if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then array
enter into C2:C700

=ArrayUniques(IF(A2:A700<>"",A2:A700,""))

and enter into D2:D700

=SUM(vlookups(C2,A$1:B$700,2)) and copy it down as far as needed

Alan Beban
 
Hello,

Column 1 contains a product ID and could contain just one entry or several
entries depending upon how many reels of that product ID we have in
inventory. Column 2 has the reel quantity in feet. I need to get a total
quantity in feet for each product ID.

Column 1 Column 2
Prod ID Feet
ABC 100
ABC 150
ABC 200
XYZ 50
XYZ 50

I need the total product, in this example ABC = 450 ft and XYZ = 100 ft. I
have over 3000 lines and over 600 products I can't do it manually. I think a
pivot table would do it but I'm not sure...

tks,
steve
Click to expand...

A Pivot table should do fine.

Drag the Prod ID to Rows; and drag the Feet to Data.


--ron
 
You could do a unique filter on Column 1 to summarise all the Product ID's
into a single list

Data>Filter>Advanced Filter>Copy to Another Location>Unique Records Only

Then use a sumif function in the coloumn adjacent to the filtered list to
determine how much is in each product ID

=SUMIF(A:A,D2,B:B)
 
Back
Top