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

Hi,

Please show us the source data.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Michelle" wrote in message
...
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

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Michelle" wrote:

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

Hi

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

"Shane Devenshire" wrote:

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Michelle" wrote:

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

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

"Ashish Mathur" wrote:

Hi,

Please show us the source data.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Michelle" wrote in message
...
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

Double unary minus:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

"Michelle" wrote in message
...
Hi

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

"Shane Devenshire" wrote:

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Michelle" wrote:

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

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.

"David Biddulph" wrote:

Double unary minus:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

"Michelle" wrote in message
...
Hi

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

"Shane Devenshire" wrote:

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Michelle" wrote:

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

Excel 2007 Pivot Table
Subtotal %
http://www.mediafire.com/file/madlmz...03_24_09a.xlsx