Pivot Table Percentage of Sub-Total

M

Michelle

I have searched through all the posts and can't find the answer I am looking
for. I have a pivot table where I am trying to get the percentage of the
subtotal for each item, but it only lets me get the percentage of the grand
total. I saw a previous example which basically summed up the percentages
which not work in my case.

The following is what I would like to see...but I keep getting either #n/a
or percentages of the grandtotal which are not going to work.

Vehicle Dept Miles Percentage
Car 1 A 10 10%
B 10 10%
C 80 80%
Car 1 Subtotal 100 100%
Car 2 A 20 20%
B 80 80%
Car 2 Subtotal 100 100%
Grandtotal 200
 
S

Shane Devenshire

Hi,

Pivot tables don't give us access to the Subtotal fields. You could create
this in the spreadsheet using formula:

Assume your data is in A1:C100 where column A contains the Car 1, column B
contains A, B, C and column C contains the values.

Assume you set up the following report area in the range E1:H5:

Car 1 A 10 10%
Car 1 B 10 10%
Car 1 C 80 80%
Car 2 A 20 20%
Car 2 B 80 80%

The formulas in G1 and H1 are

=SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7)

=SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7)/SUMIF(A$2:A$7,E1,C$2:C$7)
 
M

Michelle

Hi

I finally was able to find my post. What does the -- represent in your
formula?
 
M

Michelle

Hi

I was finally able to find my post. The source data would be like the
following. We have a number of different vehicles that can be checked out by
various departments. When it comes time to calculate the insurance cost each
department will pick up, I need to calculate the percentage for each
department. It doesn't give me the correct percentages unless I manually
calculate it...well there are like 50 some vehicles.

Vehicle Department Miles
Dodge1 Dept1 20
Dodge1 Dept2 10
Dodge1 Dept1 10
Chevy1 Dept3 20
Chevy1 Dept3 15
Chevy1 Dept2 20
Chevy2 Dept3 20
Chevy2 Dept4 25
 
M

Megan

Ok...I have a problem though as I don't know what the percentage is. That is
what I am trying to calc.

The source data would be like the following. We have a number of different
vehicles that can be checked out by various departments. When it comes time
to calculate the insurance cost each department will pick up, I need to
calculate the percentage for each department. It doesn't give me the correct
percentages unless I manually calculate it...well there are like 50 some
vehicles.

Vehicle Department Miles
Dodge1 Dept1 20
Dodge1 Dept2 10
Dodge1 Dept1 10
Chevy1 Dept3 20
Chevy1 Dept3 15
Chevy1 Dept2 20
Chevy2 Dept3 20
Chevy2 Dept4 25

I hope this makes sense...thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average of Subtotals in Pivot Table 0
Pivot Table Percent Question 4
Percentage of a "sum of" amount in a different field of a pivot re 2
Sum of each row. 4
Percent of total in Pivot tables 1
Pivot Table percentages 1
adding figures that match 2 true and 1 false if statement conditio 5
basic question - how to calculate a cumulative percentage? 3

Top