Function Problems

How to Count Unique Values by Category in Excel for Mac

Steve K Sep 28, 2026 870 views

Question details

The user wants to find a way to count the number of distinct items within specific categories in an Excel worksheet.

How to Count Unique Values by Category in Excel for Mac

Product: Excel for Mac
Device & OS: Mac
Scenario: Summarizing a dataset to find the exact number of unique items belonging to different categories, such as calculating the count of distinct fruit types versus vegetable types.
Observed behavior: The user needs a functional method, either via dynamic array formulas or Pivot Tables, to output category names alongside the accurate count of their unique items.

Before you start

Ensure you are using Microsoft 365 or a recent version of Excel for Mac that supports dynamic array formulas, as older versions may require a different, more complex formula structure.

Solution 1Recommended

Use Dynamic Array Formulas (UNIQUE and FILTER)

Combine modern Excel functions to instantly extract and count distinct items based on a designated category.

This method utilizes dynamic arrays which update automatically when data changes. It is the most robust approach for users on Microsoft 365 or newer versions of Excel for Mac.

Prepare your category list

Create a list of your target categories in an empty column (for example, type your unique category names in column D) that you want to calculate the counts for.

Enter the formula

Select the cell immediately next to your first category name and enter the following formula: =COUNTA(FILTER(CHOOSECOLS(UNIQUE($A$2:$B$12),1),CHOOSECOLS(UNIQUE($A$2:$B$12),1)=$A15)). Make sure to adjust the cell references so they match where your category list and item list actually reside.

Apply to all categories

Press Return to calculate the count for the first category. Click the calculated cell, grab the fill handle in the bottom-right corner, and drag it down to fill the formula beside the rest of your category list.

Use Dynamic Array Formulas (UNIQUE and FILTER)

Formula Breakdown: The UNIQUE function first filters the entire table for unique row combinations. The FILTER and CHOOSECOLS functions then isolate the items that belong specifically to your targeted category before COUNTA provides the final tally.

Solution 2

Use Pivot Tables with the Data Model

Use the built-in Pivot Table 'Distinct Count' feature by loading your data into the Excel Data Model.

Seamless Data Analysis

Easily Count Unique Values with WPS Office

WPS Spreadsheet offers powerful data analysis tools, complete compatibility with modern dynamic array functions, and advanced Pivot Table features, making it incredibly easy to extract unique values by category.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office on your Mac and open the workbook that contains the category and item data you want to analyze.
2. Insert a Pivot Table: Navigate to the 'Insert' tab on the top menu, select 'PivotTable', and confirm your data range.
3. Analyze and Summarize Data: Drag your Category field to Rows and the Item field to Values. Configure the field settings to count unique values, or simply use the same UNIQUE and FILTER formulas directly in the spreadsheet cells.

Fully compatible with Microsoft Excel file formats and advanced dynamic array formulas.Supports robust Pivot Table features for quick distinct counts without lag.Lightweight, completely free alternative that runs seamlessly on macOS, Windows, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Distinct Count option missing in my Excel for Mac Pivot Table?

The 'Distinct Count' option is only available if you check the 'Add this data to the Data Model' box when initially creating the Pivot Table. If this box is unchecked, the standard count function will be used instead.

Does the UNIQUE formula automatically update when new data is added?

If your source dataset is formatted as an official Excel Table (via Insert > Table), the UNIQUE function will automatically incorporate newly added rows. If it is just a standard range, you will need to manually adjust the cell references in your formula.

Can I count unique values with older versions of Excel for Mac?

Yes, but you will need to use a more complex legacy array formula, typically combining SUM, IF, and FREQUENCY or COUNTIFS, because older versions do not support modern dynamic array functions like UNIQUE and FILTER.

Excel & Data Problems