How to Count DISTINCT Values in a Pivot Table?

You need to create a Pivot Table and you're looking for the count distinct function in the Value options section only to find it's not there. So what do you do? This video will show you how to use the SUM, IF, FREQUENCY, and MATCH functions to count distinct values. You can then use these values to create a distinct count on a Pivot Table. This should tide you over until you upgrade to a newer version or get onto using Power Pivot or Power BI or the like.

Steps to Count Distinct Values Using SUM, IF, FREQUENCY, and MATCH Functions

1. Prepare Your Data: Ensure your data is organized in a tabular format with headers for each column.
2. Add a Helper Column: Insert a new column next to your data to use as a helper column. This column will help you identify unique values.
3. Enter the Formula: In the first cell of the helper column, enter the following formula to identify unique values:
   =IF(SUM(IF(FREQUENCY(MATCH(A2:A100, A2:A100, 0), MATCH(A2:A100, A2:A100, 0))>0, 1))>0, 1, 0)
4. Array Formula: Press Ctrl + Shift + Enter to enter the formula as an array formula. This will add curly braces around the formula.
5. Copy the Formula: Copy the formula down the helper column to apply it to all rows in your data range.
6. Sum the Helper Column: At the bottom of the helper column, use the SUM function to count the number of unique values:
   =SUM(B2:B100)
7. Create a Pivot Table: Select your data range, including the helper column, and go to the Insert tab. Click on Pivot Table and choose where you want the Pivot Table to be placed.
8. Build the Pivot Table: In the PivotTable Fields pane, drag and drop fields to build your Pivot Table. Use the helper column to display the count of unique values.

By following these steps, you can count distinct values using the SUM, IF, FREQUENCY, and MATCH functions in Excel. This method allows you to create a distinct count on a Pivot Table, even if the built-in distinct count function is not available.