How to Count Unique Values in Google Sheets Using COUNTUNIQUE

When compiling customer databases, survey responses, or sales logs, datasets frequently accumulate recurring entries. Determining the true size of your audience or product catalogue requires tallying distinct entries rather than total rows. In Google Sheets, calculating this figure without manually de-duplicating data is accomplished with the COUNTUNIQUE function.

Unlike standard spreadsheet formulas that require complex array nesting, Google Sheets features a native formula designed specifically to ignore duplicate entries and deliver a rapid tally of unique values.

Basic Syntax of COUNTUNIQUE

The syntax for COUNTUNIQUE is straightforward:

=COUNTUNIQUE(value1, [value2, ...])

Where value1 represents a cell range, list of arguments, or combined data table. The formula checks every item within the designated boundaries and counts each distinct item exactly once, regardless of how many times it repeats.

Step-by-Step: Count Distinct Entries in a Column

To count the unique values in a single column:

  1. Click into the cell where you want your summary figure to appear.
  2. Enter the following formula:
    =COUNTUNIQUE(A2:A100)
  3. Replace A2:A100 with the actual range containing your data.
  4. Press Enter.

If column A contains ten entries of “London”, fifteen entries of “Manchester”, and five entries of “Edinburgh”, COUNTUNIQUE will accurately return 3.

Counting Unique Values Across Multiple Ranges

If your dataset spans non-contiguous columns or separate tables, you can evaluate multiple cell ranges simultaneously within a single function:

  1. Select your reporting cell.
  2. Enter the formula with comma-separated arguments:
    =COUNTUNIQUE(A2:A50, C2:C50, E2:E50)
  3. Press Enter to calculate the unified unique count across all specified columns.

Alternative: Combining UNIQUE and COUNTA

Another widely adopted approach in Google Sheets is nesting the UNIQUE and COUNTA functions:

=COUNTA(UNIQUE(A2:A100))

While both methods yield identical numbers in standard lists, COUNTUNIQUE is more efficient and handles blank cells cleanly without requiring additional error filtering.

Frequently Asked Questions

Does COUNTUNIQUE differentiate between uppercase and lowercase text?

No. By default, COUNTUNIQUE in Google Sheets is case-insensitive. It treats “Apple”, “apple”, and “APPLE” as the same unique entry and counts them as one.

Does the formula count empty or blank cells?

No. COUNTUNIQUE automatically ignores empty cells within the selected range, ensuring blank rows do not artificially inflate your distinct count.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.