CODEWITHSUNDEEP
excelpivot-table
BoCoKeith
BoCoKeith

Reputation: 946

Excel PivotTable Count field as % of another Count field

I am using Excel from Microsoft Office 365 ProPlus.

Here's a really simple data table.

enter image description here

I want to build a Pivot Table around it to look like this...

enter image description here

... except that the "what I want" column (which is the count of items in Column C divided by the count of items in Column B) should be a part of the pivot table.

I have tried all sorts of things using calculated fields, calculations on fields, etc., to add the "what I want" column and just cannot make it work.

Can anyone help?

Upvotes: 1

Views: 10410

Answers (3)

Jason Ryan
Jason Ryan

Reputation: 1

Worked great after changing my columns from counts (counting # of "yes") to sums (by changing "yes" to 1).

Upvotes: 0

Michael
Michael

Reputation: 4838

Calculated Fields only operate on the Sum of the elements in the data tables. Wherever you see a Field Name in the formula for a Calculated Field, picture it as meaning the sum of all elements for that field (that match any other row/column criteria in the Pivot Table).

Putting "= B / C" actually means "= SUM(B) / SUM(C)" for elements of columns B and C that fit that section of the Pivot Table.

The only way to achieve your goal is with two helper columns:

B Count: =COUNT([@B])

C Count: =COUNT([@C])

The sum of these columns then give you the count of columns B and C, so you can use these helper columns to give you what you want:

Img

The Data Field based on the Calculated Field then says "Sum of What U Want", but it will always just be the result of your calculation, even if you change how the field is summarized through Value Field Settings. You can manually rename the Data Field, but it still needs to be different to the Calculated Field name you chose earlier.

Upvotes: 3

tysonwright
tysonwright

Reputation: 1525

Click in the Pivot Table, then go to the Analyze tab, click on Fields, Items, & Sets, then select Calculated Field. Your formula is probably 

= B / C

Upvotes: -1

Related Questions