Excel for Mac does not offer Distinct Count as a summary option in a standard PivotTable. On a Mac, use a formula or a helper column to count unique items; for Excel’s built-in PivotTable Distinct Count, create a PivotTable from the Data Model in Excel for Windows.
What Distinct Count means in a PivotTable
A regular PivotTable can summarize a field with operations such as Sum, Count, and Average. Count counts nonblank entries, not unique values. If a customer appears in five sales rows, a Count of the customer field adds five, while a distinct count adds one for that customer.
This difference matters whenever your source has repeated records. For example, a sales report might need to show the number of unique customers per region, even though each customer can have several orders. Counting order rows answers a different question: how many records or transactions are present?
In Excel, the built-in Distinct Count aggregation is associated with PivotTables that use the Data Model. It is not simply another option that appears for every PivotTable. Excel for Mac can create and use ordinary PivotTables, but does not provide the same Data Model authoring workflow as Excel for Windows. That is why searching the Mac field settings for a Distinct Count choice usually leads nowhere.
Check whether your PivotTable can do it
On Mac, select a value in the PivotTable and look at the value-field summary options. Depending on the Excel version, the command may be labelled Value Field Settings or appear under a similar field-settings menu. If the available summaries include Count but not Distinct Count, that is expected for a standard PivotTable; it does not mean the source data is broken.
There is an important distinction between opening a workbook and creating its model. A workbook made in Windows with a Data Model PivotTable may open on a Mac, but the available editing, refresh, and model-management capabilities can differ by Excel build. If a report depends on that model, test the specific workbook and refresh process on the Mac before relying on it for recurring work. For a Mac-first workflow, formulas or a helper column are generally easier to maintain.
Count unique values with UNIQUE and FILTER
For a single total, a dynamic-array formula is often the simplest alternative. UNIQUE returns one copy of each value, and FILTER can remove blank cells before the count. UNIQUE and FILTER are available in Microsoft 365 and Excel 2021; check the version if the formula is not recognized. These functions are available in current Microsoft 365 Excel for Mac as well as supported Windows editions.
Assume customer IDs are in B2:B7 and contain C-17, C-22, C-17, C-31, a blank, and C-22. With the IDs in B2:B7, enter this formula in an empty cell:
=COUNTA(UNIQUE(FILTER(B2:B7,B2:B7<>"")))
The result is 3: C-17, C-22, and C-31 are the three distinct nonblank IDs. The source range should cover all relevant rows. If the data grows, either expand the range or use a well-maintained Excel Table as the source for your reporting process. A fixed reference such as B2:B7 will not automatically include data added in row 8.
This method returns a worksheet result, not a new PivotTable summary. You can place the formula beside a report, or use it to check the totals in a PivotTable. It is particularly convenient when you need just one unique count and do not need PivotTable-style rearranging or filtering.
Calculate a distinct count for each category
To calculate unique customers by region, use FILTER to keep the rows for one region and UNIQUE to remove repeated customer IDs. For example, assume regions are in A2:A7, customer IDs are in B2:B7, and the region to report is in E2. The rows contain West/C-17, East/C-17, West/C-22, West/C-17, East/blank, and East/C-31. With West in E2, enter:
=IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$7,($A$2:$A$7=E2)*($B$2:$B$7<>"")))),0)
The result is 2 for West: C-17 and C-22. The multiplication in the FILTER condition requires both tests to be true: the region must match E2 and the customer ID must not be blank. IFERROR returns zero if the selected region has no qualifying rows. Copy the formula down beside a list of region names to calculate the count for each one.
Be clear about what makes an item unique. If two people share a name, counting names will treat them as one person; a customer ID is usually a better field. Extra spaces can also make entries that look alike behave as different values. Clean inconsistent source data before interpreting the result.
Use a helper column with an ordinary PivotTable
If you want the familiar PivotTable layout on Mac, mark the first occurrence of each region-and-customer pair with 1 and later occurrences with 0. Then sum that helper field in a standard PivotTable. Assume region is in A, customer ID is in B, row 2 is the first data row, and the helper formula goes in C2. Enter this formula and fill it down:
=IF(B2="",0,IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,0))
For each row with a customer ID, COUNTIFS checks how many times that same region-and-customer combination has appeared from the first data row through the current row. The first occurrence receives 1; repeats receive 0. A blank customer ID receives 0. The formula uses ordinary Excel functions and does not require the Data Model.
Next, create a PivotTable from the source range including column C. Put Region in Rows and the helper field in Values. In the value-field settings, choose Sum rather than Count. Each region’s sum is its distinct customer count. For example, if West has three rows for C-17 and one for C-22, the helper values for those records total 2, not 4.
The helper column depends on the order of the records only to identify which occurrence is first; the final sum remains the unique count as long as the formula is filled through the data. When new rows are added, extend the formula and the PivotTable source, then refresh the PivotTable. Using an Excel Table can make it easier to keep the source and formulas expanding together.
Use the built-in option in Excel for Windows
If you have access to Excel for Windows, the native approach is to create a PivotTable and select the option to add its source to the Data Model. Then place the desired field in Values, open its value-field settings, and choose Distinct Count as the summary. The exact placement of the checkbox can vary slightly by Windows version and the PivotTable creation dialog, but the key requirement is that the PivotTable use the Data Model.
This is a useful route when a workbook already uses relationships between multiple tables or when a large report needs model-based analysis. Save the workbook in a format that preserves the model, and confirm that Mac users can open and refresh it as needed. If collaborators only need to view the result, a prepared report may be enough; if they must change the model or refresh external sources, verify those tasks on their own Excel platform.
Choose the right Mac workaround
- Use UNIQUE and FILTER for a quick total or a compact report by category when dynamic-array formulas are available.
- Use a helper column when the result must appear inside a conventional PivotTable that Mac users can rearrange and filter.
- Use a Windows Data Model PivotTable when you specifically need the built-in Distinct Count aggregation or the broader Data Model features.
- Use a Power Query workflow when you regularly import and clean data. Power Query availability and supported operations vary by Excel for Mac version and data source, so verify the needed steps in your installation before designing a refresh process.
There is no single best workaround for every workbook. A formula is concise but can be harder for a colleague to understand if it is buried in a large sheet. A helper column is more visible and works naturally with ordinary PivotTables, but adds a field that must be maintained. A Windows-created model can be powerful, but introduces platform and refresh considerations.
Common problems and checks
- Count is larger than expected: the PivotTable is counting records, not unique IDs. Use a distinct-count-capable Data Model or one of the Mac workarounds above.
- The formula returns an error: confirm that your Excel version supports UNIQUE and FILTER, check that the ranges have matching sizes, and make sure the output area is clear for the formula result.
- The count includes a value you consider blank: a cell containing spaces is not truly empty. Remove or clean whitespace in the source, since a test for an empty string does not remove ordinary spaces.
- The PivotTable total is stale: refresh the PivotTable after changing its source. Formula results and PivotTable caches are separate; changing the source does not always update the displayed PivotTable immediately.
- The helper total is too high: verify that the helper formula uses both the category and ID columns in COUNTIFS. Counting only the ID would merge the same customer across different regions.
Also decide whether blanks should count as a distinct item. The examples here exclude blank IDs, which is usually appropriate for customer counts. If a blank represents a meaningful category in your analysis, define that rule explicitly and adjust the formula or helper logic rather than assuming Excel will interpret the blank the way your report intends.
Frequently Asked Questions
Can Excel for Mac do a distinct count in a PivotTable?
Not as a built-in summary in a standard Mac-created PivotTable. Use a UNIQUE/FILTER formula or a helper column, or create a Data Model PivotTable in Excel for Windows.
Why is Distinct Count missing from Value Field Settings?
Distinct Count is available for PivotTables using the Data Model, not every ordinary PivotTable. Excel for Mac lacks the same Data Model creation workflow as Windows, so the option is generally absent when you create a standard PivotTable on Mac.
Can I count unique customers by month or region?
Yes. Filter the ID range by the relevant month or region, then count UNIQUE results, or use a helper column that identifies the first occurrence within each group and sum it in a PivotTable.
Does UNIQUE count blank cells?
UNIQUE can return a blank as one of its unique results. In the examples above, FILTER removes blank IDs first so that missing customer values do not inflate the count.
Will a Windows Data Model PivotTable work on my Mac?
A workbook containing one may open, but editing and refreshing can depend on the Excel for Mac version, workbook connections, and model features used. Test the exact workbook and refresh requirements rather than assuming full Windows feature parity.
Related
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Move Down a Line in Excel: Cells, Rows, and TextIn Excel for Microsoft 365 on Windows, press Enter to finish entering a value and move the selection…
- Excel Date Turns Into Hashtags? Causes and FixesIf an Excel date turns into hashtags such as #####, the cell usually cannot display the formatted value…
- Excel: How to Remove Data, Duplicates, and MoreIn Excel, the right way to remove something depends on what you want gone: press Delete to clear…
- Excel Shortcut to Delete a Row (Windows and Mac)In Excel for Microsoft 365 on Windows, select a cell in the row and press Shift+Space to select…
- How to Make a PowerPoint Like a Trifold Poster LayoutTo make a PowerPoint trifold poster, set one slide to the poster’s full unfolded size, mark the three…
- Excel Cell Expand to Fit Text: AutoFit or WrapTo make an Excel cell expand to fit text, select the relevant column or row and use Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.