Subtotals or Pivot Tables

[peter]

In the following example:

For Item "a": I need subtotals of $418.38 for "CA", $67.78
for "NV" and $97.70 for "AZ" plus the total of $583.86.

For Item "b": I need subtotals of $202.16 for "ME", $33.90
for "VT" and $48.84 for "OK" plus the total of $284.90.

Item State Price
a CA 81.08
a CA 82.70
a CA 83.68
a CA 84.62
a CA 86.30
a NV 13.54
a NV 13.56
a NV 13.56
a NV 13.56
a NV 13.56
a AZ 19.54
a AZ 19.54
a AZ 19.54
a AZ 19.54
a AZ 19.54
b ME 39.18
b ME 39.96
b ME 40.44
b ME 40.88
b ME 41.70
b VT 6.78
b VT 6.78
b VT 6.78
b VT 6.78
b VT 6.78
b OK 9.78
b OK 9.76
b OK 9.78
b OK 9.76
b OK 9.76

Using my example, please give me step-by-step
instructions.

Thanks

Peter

 

[Ken Wright]

Using Pivot tables - You don't say what version so instructions may differ
slightly:-

Highlight your data - headers as well and then do Data / PivotTable and
PivotChart Report
Hit Next
Hit Next
Hit Finish

You will now see a blank table on the worksheet and a floating dialog box called
PivotTable Field List with 3 items in it, namely your headers:-

Drag ITEM to the far left of the blank table
Drag STATE to just between the ITEM field and the blank area in the middle.
Drag PRICE into the big blank area in the middle

Job Done

 

[Guest]

Ken,

I'm using Excel 2000

The blank table has several sections:

Drop Page Fields Here
Drop Column Fields Here
Drop Row Fields Here
Drop Data Items Here

What section are you referring to when you say "Drag ITEM
to the far left of the blank table"?

peter

 

[Ken Wright]

OK, 2000 is slightly different in that you don't see the floating dialog box I
referred to:-

Drag the field marked ITEM which is one of your headers and should be visible in
the wizard, over to the section where it says Drag ROW fields here. Then drag
the field marked PRICE to where it says Drag DATA items here. Then drag the
field marked STATE directly in-between where you can see the ITEM and PRICE.

Happy to send you a small demo file if you like based on your data so you can
see what you should be getting. If you do want that, then just send me an
email, don't post your email address in the groups, and take the NOSPAM bit out
of mine.