Pivot Table Issues

Count Distinct Values in an Excel PivotTable on Mac

Bushra ParveenBushra Parveen Nov 12, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select a blank result cell

Click an empty cell next to the source data where you want the distinct count to appear.

2
Enter the distinct-count formula

Type =COUNTA(UNIQUE(A2:A1000)) and press Enter. Excel calculates the number of unique nonblank values returned by UNIQUE.

3
Display the unique items if needed

To list the distinct values instead of only counting them, select an empty cell and enter =UNIQUE(A2:A1000).

Check blank values: If the source range contains blanks and your result should exclude them, use a filtered range such as =COUNTA(UNIQUE(FILTER(A2:A1000,A2:A1000<>""))).
WPS Office solution

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. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, and open the XLSX workbook containing the repeated values.
  2. 2. Enter a distinct-count formula: Select a blank cell and enter =COUNTA(UNIQUE(A2:A1000)), replacing the range with your source values.
  3. 3. List unique items when needed: Select another blank cell and enter =UNIQUE(A2:A1000) to spill the distinct items into the worksheet.
  4. 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.
Use UNIQUE and COUNTA to calculate a distinct count quickly.Create a helper-column summary when dynamic-array formulas are unavailable.Open, edit, and save Microsoft Excel XLSX workbooks with compatible formatting.Use a familiar spreadsheet interface with lightweight tools for everyday analysis.
microsoft office alternative - wps office

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.