ThisCalculator

Everyday

Count Unique Values with COUNTIF in Excel

Updated · Standard formula, shown below

Unique / Total 70.0 %
Range size
10 items
You entered
Unique values
7 items
You entered

Yes, you can count unique values in a range using COUNTIF, but COUNTIF alone counts occurrences, not unique entries. To get a true unique count, you combine COUNTIF with SUMPRODUCT (or SUM) and a simple division trick: =SUMPRODUCT(1/COUNTIF(range, range)). This article explains that formula, why it works, its limitations, and the alternatives you have in Excel and Google Sheets.

Formula

The classic formula for counting unique values in a range (excluding blanks) is:

=SUMPRODUCT(1/COUNTIF(range, range))

Where:

  • range – the cells you want to count unique values in (e.g., A2:A100)
  • COUNTIF(range, range) – returns an array where each cell shows how many times its value appears in the whole range
  • 1/COUNTIF(…) – converts each occurrence to a fraction (e.g., a value appearing 3 times becomes 1/3 for each of its 3 cells)
  • SUMPRODUCT – sums those fractions; each distinct value sums to exactly 1

If a value appears 4 times, each of its 4 cells contributes 1/4, and 4 × 1/4 = 1. So the total equals the number of distinct values.

Worked Example

Suppose you have the following list in cells A1:A10:

Apple, Banana, Apple, Orange, Banana, Apple, Grape, Orange, Kiwi, Banana

Step 1: COUNTIF returns: {3, 3, 3, 2, 3, 3, 1, 2, 1, 3} (Apple appears 3 times, Banana 3, Orange 2, Grape 1, Kiwi 1).

Step 2: Divide 1 by each: {1/3, 1/3, 1/3, 1/2, 1/3, 1/3, 1, 1/2, 1, 1/3}.

Step 3: Sum: 1/3+1/3+1/3 = 1 (Apple), 1/2+1/2 = 1 (Orange), 1+1 = 2 (Grape and Kiwi), and Banana’s three 1/3s = 1. Total = 5 unique values.

So the formula returns 5.

Why This Works (The Math Behind It)

The formula is a clever use of weighting. Each distinct value gets a total weight of exactly 1, regardless of how many times it appears. If a value appears 5 times, each cell gets 1/5, and five cells sum to 1. If a value appears once, it gets 1/1 = 1. Summing all these weights gives the count of distinct values.

This works because the COUNTIF function, when given a range as the criteria, returns an array of counts – one for each cell. The SUMPRODUCT function then sums the array after you apply the reciprocal.

Common Problems and How to Fix Them

  • Blank cells cause #DIV/0! – COUNTIF counts blanks as 0, so 1/0 is an error. Fix: use =SUMPRODUCT((range<>"")/COUNTIF(range, range&"")) or the newer =COUNTA(UNIQUE(range)).
  • Text vs. numbers – The formula treats “123” (text) and 123 (number) as different. Use consistent data types.
  • Leading/trailing spaces – “Apple” and “Apple ” are seen as different. Clean data with TRIM() first.
  • Case sensitivity – COUNTIF is not case-sensitive, so “APPLE” and “apple” count as the same. If you need case-sensitive, use SUMPRODUCT with EXACT.
  • Large ranges slow down – The formula calculates an array for every cell. On tens of thousands of rows it can lag. Consider using a PivotTable or Power Query.

Alternatives to COUNTIF for Unique Counts

Depending on your Excel version, you have better options:

  • Excel 365 / 2021: =COUNTA(UNIQUE(range)) – the simplest and fastest.
  • PivotTable: Drag the field to Rows, then count the row items – no formula needed.
  • Advanced Filter: Copy unique values to a new location, then count them.
  • Google Sheets: =COUNTA(UNIQUE(range)) works the same way.
  • VBA macro: For legacy Excel, a small script can loop through cells and count unique values.

How to Count Unique Values with Multiple Criteria

If you need to count unique values that meet a condition (e.g., only “Active” status), use:

=SUMPRODUCT((range1="Active")/COUNTIFS(range2, range2, range1, "Active"))

This works similarly, but COUNTIFS adds the condition. For example, if you have a list of orders with product names and statuses, this returns the number of distinct products that are “Active”.

Frequently Asked Questions

Why does my COUNTIF unique formula return #DIV/0!?

Because your range contains blank cells. COUNTIF returns 0 for blanks, and dividing by 0 causes the error. Use the blank-safe version: =SUMPRODUCT((range<>"")/COUNTIF(range, range&"")).

Can I count unique values with COUNTIF alone?

No. COUNTIF alone counts how many times a specific value appears. To count distinct values, you need to combine it with SUMPRODUCT or use the UNIQUE function (in newer Excel).

Does COUNTIF count duplicates as one?

No, COUNTIF counts every occurrence. For example, COUNTIF(A1:A10, “Apple”) returns the number of cells that contain “Apple”, not whether it’s unique. To count unique values, you need the array formula.

How do I count unique values in Google Sheets?

Use =COUNTA(UNIQUE(A1:A100)). This is simpler than the Excel SUMPRODUCT method and works in all modern versions of Google Sheets.

Is there a faster way than SUMPRODUCT for large data?

Yes. For large datasets, use a PivotTable (drag the field to Rows and then count the row labels) or Power Query (Group By with “Count Distinct Rows”). These are much faster than array formulas.

How do I count unique values ignoring blanks?

Use =COUNTA(UNIQUE(FILTER(range, range<>""))) in Excel 365, or the blank-safe SUMPRODUCT formula mentioned above.

Related

Formula used
in rng | Range size | items | 10 | 0
in uniq | Unique values | items | 7 | 0
out ratio | Unique / Total | % | uniq / rng * 100 | 1

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.