Creating Calculated Field with SUM and COUNT of Fields Pivot Table

Question

Here's a snapshot of my pivot table.

I want to create a calculated field that displays the percentage from the Responded column (C) over the Ticket # column (B) as in column G.

The Ticket # column is a column of value text strings (e.g. 413456, 464313) and the Responded column is a list of 1's and 0's. In my pivot table I am taking the count of all Ticket # and the sum of the Responded.

I tried to create a calculated field with the following formula

=Responded/COUNT('Ticket #')

but this gave me the same as Sum of Responded. How can I create a calculated field that gives me the % in column G?

Community · Accepted Answer · 2017-05-23 12:14:46Z

7

From my experience and what another SO member posted, COUNT doesn't seem to work well in pivot table field calculations. I managed to count the distinct tickets by creating a helper column of 1's and 0's in my data source. (Thanks to this response from Siddarth Rout.)

Once you have 1's associated with each distinct tickets, I simply created the calculated with Responded/Ticket #.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jul 18, 2016 at 18:10

Concerned_Citizen

6,89519 gold badges61 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SherlockSpreadsheets Over a year ago

I wanted a simple COUNT calculation which could be used in other calculations (i.e.: SUM(Field_1)/RowCount. What worked best is to add a field to the source Excel Table called 'Records' with a numeric value '1' for every record; Then the Pivot Field Calculation is SUM(Field_1) /Records.

Collectives™ on Stack Overflow

Creating Calculated Field with SUM and COUNT of Fields Pivot Table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related