Forum Discussion
Solved
Pivot Table
I'm trying to have a pivot table count the number of different districts in a column. They are uniquely named. Putting Districts into the value field with count value just counts the number of cells. How can I ask it to count how many different district names there are in the column?
McKinney245 In a regular pivot table you don't have the distinct count option. That's only when you add it to the Data Model (PC only), as described in the link below.
https://exceljet.net/pivot-table/pivot-table-unique-count
If that's not an option, add a helper column to the data set with a formula like below.
Let's say the Districts at in column A, row 2 and down. Enter the following in a new column on row 2:
=--(COUNTIF($A$2:A2,A2)=1)
and copy it all the way down. This will produce a range of 1's and 0's that you can just SUM or put in the pivot table Value field where it will be summed automatically.
Or, if you are on MS365 or Excel 2021, use COUNTA combined with UNIQUE.
McKinney245 In a regular pivot table you don't have the distinct count option. That's only when you add it to the Data Model (PC only), as described in the link below.
https://exceljet.net/pivot-table/pivot-table-unique-count
If that's not an option, add a helper column to the data set with a formula like below.
Let's say the Districts at in column A, row 2 and down. Enter the following in a new column on row 2:
=--(COUNTIF($A$2:A2,A2)=1)
and copy it all the way down. This will produce a range of 1's and 0's that you can just SUM or put in the pivot table Value field where it will be summed automatically.
Or, if you are on MS365 or Excel 2021, use COUNTA combined with UNIQUE.
Thank you! The Data Model route worked. I tried the helper column too just for kicks and that was good too!
