Weird CountIf request

[Guest]

I have a long list of items with amounts sold and unit prices. I want to add
up anything that is the same item and price.

Example:
Qty Item: Price
1 TC001 $23
15 TC001 $20
3 TC001 $23
2 TC002 $44
2 TC003 $231
2 TC001 $23
1 TC003 $231
6 TC002 $40

Should return something like

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

I will have many different items and different prices.

TIA!

Rich

 

[Guest]

I would use a pivot table. Info available here:
http://www.cpearson.com/excel/pivots.htm

Dave

 

[Guest]

Hmmm.... Looks confusing. I'll give it a go.

So it will output a table for me? Like this?

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

 

[Guest]

Yes pivot tables can be formatted in that manner. You'll find they're a
fairly powerful tool for summarizing a lot of data quickly.

Dave

 

[Roger Govier]

Hi Rich

In addition to what DaveF has said, you would need to add a calculated
field to the PT to generate exactly what you want.
Mark your block of data>Data>Pivot Tables>Next>Layout
Drag Item to the Row area, drag Qty to the Row area below Item
Drag Price to the Data area, then double click on the field and choose
Sum>click OK>Finish
On the new tab with the PT, from the PT toolbar, use the dropdown and
select Formulas>Calculated field
In the Name box Type Value and in the Formula box type = Qty * Price>OK
Now on the PY, drag the Data button across to the word Total, and you
will have Price and Value side by side.

If there are subtotals appearing and you don't want them, then double
click the field heading and choose Subtotals>None

For more information on how to set up and use PT's take a look at the
following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx