Totaling repeat items in a long list.

[Guest]

Here is my problem. I have a list of hundreds of recurring items and want to
see a total for each.

Example

Item # Item Descrip Sold Cost
1001 Watch 10 $5
2002 Hat 5 $2
3003 Shirt 4 $3
1001 Watch 3 $5
2002 Hat 2 $2
3003 Shirt 5 $3


And I want it to read
1001 Watch 10 $5
1001 Watch 3 $5
Watch Total $65
2002 Hat 5 $2
2002 Hat 2 $2
Hat Total $14
3003 Shirt 5 $3
3003 Shirt 4 $3
Shirt Total $27


I don’t need the separator line, but need the total for each product. I know
how to sort and multiply in the formula, but I can't get that total without
forcing a summation for each product.

Any help would be greatly appreciated.

 

[Guest]

Assume data in columns A to D


=SUMPRODUCT(--(A2:A100=item#),(C2:C100)*(D2100))

Item# is cell containing 1001, etc

HTH

 

[Dave Peterson]

I'd either sort the data by the item number and then follow the data|subtotals
wizard to get the subtotals that you want--and then use the outlining symbols to
the left to hide the details.

Or I'd learn about data|Pivottables. With just a little bit of time spent
learning, you can get some very nice summary tables.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Guest]

That works, but it still needs me to force the item number in the formula. I
have dozens of item numbers.

I guess another way to say it is, I want excel to notice a change in a list
values, and total all of the identical values. I want it to recognize row 1
and 2 are the same, but row 3 is different, so add 1 and 2.

A longer version would be, row 15,16,17,18, - 24,25,26 are all the same.
Row 27 is different. Sum rows 15-26.