Pivot table showing a total and a percentage

mayday1

Board Regular
Joined
Oct 5, 2007
Messages
241
I have accounts which have sub accounts and each sub account is a separate row in my data. I need the "closed" percentage of each department in a Pivot Table.
ACCOUNT and STATUS are the column headings in my data

ACCOUNT, STATUS
acct#1, closed
acct#1, open
acct#1, closed
acct#1, open
acct#1, open
acct#1, open
acct#1, open
acct#1, closed
acct#1, open
acct#1, closed
acct#2, closed
acct#2, open
acct#2, closed
acct#2, open
acct#2, open
acct#2, open
acct#2, open
acct#2, closed
acct#2, open
acct#2, open

From these data I know how to do a pivot table that shows me total count of Account 1 and account 2. I can also get total of account 1 & 2 that are Closed. What I don't know is how to get a Pivot Table that shows me that PLUS the percent of each type of account that is Closed. Looking for output like this:
Acct#1 5, 10, 50%
Acct#2 4, 10, 40%

That last column has to be a formula somehow. I know how to get the Closed percentage of the total (in this example 5 out of 20 and 4 out of 20) but I need to show it as a percentage of each account type - as in each row of data.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Do you have Status as a Column field?
Otherwise you will have to PowerPivot (or PowerBI Desktop) to make the Pivot Table show this.
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top