Forum Discussion
Pivot Table
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!
