Pivot table sum issue

[Phil Smith]

My data looks so,ethign like this:


Item Country Cost Sale
Table USA 5 10
Canada 7 12
Foreign 22 56
Chair USA 3 10
Canada 4 9
Foreign 9 21

Etc.

I want to subtotal Cost and Sale for each change in Item, ONLY at each
change in item.

Item Country Cost Sale
Table USA 5 10
Canada 7 12
Foreign 22 56
Total 34 78
Chair USA 3 10
Canada 4 9
Foreign 9 21
Total 16 40

I can't put both in the data area, and it makes no sense to put them in
the coloum fields, as far as I can tell. However, when I put subtotals
on cost and sale, it also breaks on cost and sale. I wnat to subtotal
cost and sale, but break ONLY on Item. How?

Thanx

 

[Debra Dalgleish]

You can put both Cost and Sale in the Data area.
Then, drag the Data field button onto the cell that says "Total".
There's a picture here:

http://www.contextures.com/xlPivot02.html

 

[Phil Smith]

Thanx Debra, but no dice. Could this be a 2007 feature? I am working
with 2003...

 

[Debra Dalgleish]

No, those instructions are for 2003.
What happens when you try to put both Cost and Sale in the Data area?
Are they separate fields in the source data?

 

[Phil Smith]

As soon as I throw in the second field, I get an error concerning too
many fields. Yes, they are seperate coloums in my data table.

 

[Debra Dalgleish]

The following article shows the limits in Excel 2003 pivot tables.

http://support.microsoft.com/kb/820742/

Perhaps you can move the Item field to the Page area of the pivot table.
Then try to add the second data field, and drag the Data button to the
Total cell, as I described earlier.

If that works, you may be able to move the Item field back to the Row area.