How to Count Unique Values Based on Another Column in Excel

Data analysts often struggle to isolate and count unique entries within segmented datasets, which leads to manual errors and lost productivity. While traditional Excel tools like Pivot Tables or standard SUMPRODUCT formulas offer standard workarounds, they often lack dynamic flexibility. Fortunately, leveraging modern formula combinations grants you immediate, automated clarity over complex data. A critical stipulation to keep in mind is that this optimized approach requires Excel 365 or Excel 2021 to support dynamic arrays. For instance, counting unique client IDs based on specific regional sectors demonstrates the formula's real-world efficiency. Below, we break down the step-by-step formula to streamline your reporting.

In data analysis, counting unique items is a routine task. However, things get trickier when you need to count unique values only if they meet certain criteria. For instance, you might need to find the number of unique customers who made a purchase in the "East" region, or the number of unique projects assigned to a specific department.

Historically, solving this problem in Microsoft Excel required complex, resource-heavy array formulas. Fortunately, with the introduction of Dynamic Arrays in modern Excel (Excel 365 and Excel 2021), this task has become incredibly simple. In this comprehensive guide, we will explore both modern and legacy formulas to count unique values based on another column, handle multiple criteria, and manage potential errors.

The Sample Dataset

To illustrate these techniques, we will use the following sample sales table (range A1:C11):

Our goal is to count the number of unique Sales Reps who sold products in the "East" region.

Method 1: The Modern Way (Excel 365 & Excel 2021+)

If you are using Excel 365 or Excel 2021, you have access to Dynamic Array functions. The most efficient way to solve this is by nesting three functions: FILTER, UNIQUE, and ROWS (or COUNTA).

The Formula

=ROWS(UNIQUE(FILTER(B2:B11, A2:A11 = "East")))

How It Works Step-by-Step

1. FILTER(B2:B11, A2:A11 = "East"): This function looks at the Region column (A2:A11) and extracts only the corresponding Sales Reps (B2:B11) where the region is "East".
   Result: {"Alice"; "Bob"; "Alice"; "David"; "Bob"; "Alice"}
2. UNIQUE(...): This function takes the filtered list of names and removes all duplicate entries.
   Result: {"Alice"; "Bob"; "David"}
3. ROWS(...): This function counts the number of rows in the unique array. Since our unique list has 3 rows, the final output is 3.

Why Use ROWS instead of COUNTA?

While COUNTA is often used to count items, it has a subtle bug when paired with FILTER. If the FILTER function finds no matches, it returns a #CALC! error. COUNTA will count this error cell and return 1 instead of 0. Using ROWS combined with error handling is a much safer practice.

To build a bulletproof formula that returns 0 if no matches are found, wrap it in IFERROR:

=IFERROR(ROWS(UNIQUE(FILTER(B2:B11, A2:A11 = "North"))), 0)

Method 2: Handling Multiple Criteria (Modern Excel)

In real-world scenarios, you often need to filter by more than one column. For example, let's count the number of unique Sales Reps in the "East" region who sold "Widgets".

To apply multiple criteria inside the FILTER function, use the multiplication operator (*), which acts as the logical AND operator.

The Formula

=ROWS(UNIQUE(FILTER(B2:B11, (A2:A11 = "East") * (C2:C11 = "Widgets"))))

How It Works

• (A2:A11 = "East") returns an array of TRUE/FALSE values: {TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}.
• (C2:C11 = "Widgets") returns another array of TRUE/FALSE values based on the product column.
• Multiplying these two arrays together treats TRUE as 1 and FALSE as 0. A 1 is returned only if both conditions are met.
• The FILTER function evaluates this multiplied array, extracting the unique Sales Reps matching both criteria.

Method 3: The Legacy Way (Excel 2019 and Older)

If your organization is still using legacy versions of Excel (such as Excel 2016 or 2019), the FILTER and UNIQUE functions are not available. You must rely on older formula constructions using SUMPRODUCT and COUNTIFS.

The Formula

=SUMPRODUCT(((A2:A11="East") / COUNTIFS(A2:A11, A2:A11, B2:B11, B2:B11)) * (A2:A11="East"))

Note: If your dataset contains blank rows, this formula can return a division-by-zero error (#DIV/0!). To bypass this issue, we must expand the formula.

A Safer Legacy Version (Handles Blanks)

If there are empty cells in your range, use this array formula. In older Excel versions, you must press Ctrl + Shift + Enter instead of just Enter to activate it:

=SUM(IF(A2:A11="East", 1/COUNTIFS(A2:A11, "East", B2:B11, B2:B11&""), 0))

How the Legacy Formula Works

1. COUNTIFS(A2:A11, "East", B2:B11, B2:B11&"") calculates how many times each rep appears within the filtered region. The &"" prevents errors from blank cells.
2. Dividing 1 by these counts (1/COUNTIFS(...)) assigns fractional weights to the duplicate values. For instance, if Alice appears 3 times in the "East", her record gets a value of 1/3. When summed up three times (1/3 + 1/3 + 1/3), it equals exactly 1.
3. The outer IF function ensures that we only calculate these fractional values for rows where the region is actually "East".
4. Finally, SUM aggregates all these fractional parts, resulting in the correct count of unique items.

Method 4: Case-Sensitive Unique Counting

By default, Excel functions like UNIQUE, FILTER, and COUNTIFS are case-insensitive. They treat "alice" and "Alice" as identical. If your data is case-sensitive, you need to incorporate the EXACT function.

Here is how you can write a case-sensitive count of unique values for "East" (where "EAST" or "east" in the region column should be ignored or evaluated precisely):

=ROWS(UNIQUE(FILTER(B2:B11, EXACT(A2:A11, "East"))))

The EXACT function performs a strict case-sensitive check, returning TRUE only when the character cases match exactly.

Summary: Which Method Should You Choose?

To help you decide which approach fits your workflow best, here is a quick comparison table:

Conclusion

Counting unique values based on criteria used to be one of Excel's most notorious challenges. Thankfully, modern Excel users can achieve this quickly using the =ROWS(UNIQUE(FILTER(...))) formula combination, which is clean, fast, and easy to modify for multiple conditions. If you are stuck on an older version of Excel, the legacy SUMPRODUCT/COUNTIFS approach still serves as a dependable alternative.