Pivot Table Subtotals

[Guest]

Please assist
I have been unsuccessfully trying to figure out a way to get the column
totals for every change in service type.

Below is sample of the pivot table

service type 1-2 days 3-9 days 10-30 days 30+
st1 12 8 3 4
st1 3 4 5 4
st1 TOTAL

st2 5 7 9 3
st2 6 4 11 2
st2 total

Thanks

 

[Guest]

did you try to select "service type" (any item or the heading) right click,
select field setting and in the pop up windows select custom and the kind of
subtotal you like?

 

[Guest]

did you try to select "service type" (any item or the heading) right click,
select field setting and in the pop up windows select custom and the kind of
subtotal you like?

 

[Guest]

Yes, I did and put a row count for each service type in a total column next
to the 30+ column.
Thanks

 

[Debra Dalgleish]

How is the pivot table set up? What fields are in the Row area, the
Column area, and the Data area?

What version of Excel are you using?

 

[Guest]

how come you have two st1 in a pivot table? are you sure they are typed
exactly the same in the source data? Or you have one more row field not shown
in the example?

 

[Guest]

Thanks for responding.
Row Area:
service type
1-2 days
3-9 days
10-30 days
30+

Column Area:
Nothing

Data Area:
Count of service area

The data is an Access crosstab query export

 

[Debra Dalgleish]

You may need to reorganize the data, so you can use it in a pivot table.
You can use the 'unpivot' technique described by John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Assuming the crosstab export looks like this:

service type 1-2 days 3-9 days 10-30 days 30+
st1 12 8 3 4
st1 3 4 5 4
st2 5 7 9 3
st2 6 4 11 2

Using the unpivot technique will create this layout:
Service Type Length Count
st1 1-2 days 12
st1 3-9 days 8
st1 10-30 days 3

And you can create the pivot table from that.

 

[Guest]

Many Thanks

You may need to reorganize the data, so you can use it in a pivot table.
You can use the 'unpivot' technique described by John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Assuming the crosstab export looks like this:

service type 1-2 days 3-9 days 10-30 days 30+
st1 12 8 3 4
st1 3 4 5 4
st2 5 7 9 3
st2 6 4 11 2

Using the unpivot technique will create this layout:
Service Type Length Count
st1 1-2 days 12
st1 3-9 days 8
st1 10-30 days 3

And you can create the pivot table from that.