Count Distinct Values in an Excel PivotTable on Mac
Question details
The user needs to count unique values in a PivotTable on a Mac when the standard Distinct Count option is unavailable.
- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- A workbook contains repeated values, and the user wants a PivotTable or worksheet result showing the number of distinct items.
- Observed behavior
- The standard PivotTable Value Field Settings menu may not show Distinct Count, depending on the Excel version and whether the workbook uses the Data Model.
Confirm the source column has a header and identify the data range, such as A2:A1000. Check whether your Excel version supports dynamic-array functions such as UNIQUE, or whether the workbook can use the Data Model.
Use UNIQUE and COUNTA to calculate the distinct count
Use this method when Excel for Mac supports dynamic-array formulas and you need a quick count outside the PivotTable.
UNIQUE returns one instance of each item, while COUNTA counts the resulting list. Replace A2:A1000 with the actual source range and exclude the header row.
Click an empty cell next to the source data where you want the distinct count to appear.
Type =COUNTA(UNIQUE(A2:A1000)) and press Enter. Excel calculates the number of unique nonblank values returned by UNIQUE.
To list the distinct values instead of only counting them, select an empty cell and enter =UNIQUE(A2:A1000).
Create a helper column and summarize it in a PivotTable
Use this version when dynamic-array formulas are unavailable or when the distinct result must remain part of a traditional PivotTable.
Use the Data Model or Power Query when available
Use this option when your Excel for Mac version supports the Data Model or Power Query and you want a model-based distinct-count measure.
Count unique values in WPS Spreadsheets without a PivotTable Distinct Count option
WPS Spreadsheets provides a practical way to calculate unique values with formulas or a helper column, while keeping the workbook easy to edit and share in Excel-compatible formats.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, and open the XLSX workbook containing the repeated values.
- 2. Enter a distinct-count formula: Select a blank cell and enter =COUNTA(UNIQUE(A2:A1000)), replacing the range with your source values.
- 3. List unique items when needed: Select another blank cell and enter =UNIQUE(A2:A1000) to spill the distinct items into the worksheet.
- 4. Use a helper column if required: Add a helper field and enter =IF(COUNTIF($A$2:A2,A2)=1,1,0), fill it down, then summarize the helper field with a PivotTable using Sum.

Frequently Asked Questions
Why is Distinct Count missing from my Excel for Mac PivotTable?
Distinct Count depends on the Excel version and whether the PivotTable is based on the Data Model. A standard PivotTable may offer only Sum, Count, Average, and similar summaries.
What formula counts unique values in Excel for Mac?
If dynamic arrays are supported, enter =COUNTA(UNIQUE(A2:A1000)) in a blank cell. Replace the range with the column or data range you want to analyze.
How can I exclude blank cells from the distinct count?
Use =COUNTA(UNIQUE(FILTER(A2:A1000,A2:A1000<>""))) when FILTER is available. This removes blank values before UNIQUE returns the list.
Can a traditional PivotTable count distinct values without the Data Model?
Yes. Add a helper column that returns 1 for the first occurrence and 0 for duplicates, then add that helper field to the PivotTable Values area and summarize it with Sum.
Can WPS Spreadsheets open the Excel workbook used for this calculation?
Yes. WPS Spreadsheets can open, edit, and save common Microsoft Excel XLSX files, allowing you to use formulas or a helper-column PivotTable workflow.




