Google Sheets: Count Unique Values in a Column Easily

To count distinct values in a Google Sheets column, use =COUNTUNIQUE(A2:A) when the data starts in A2. To count only nonblank entries and avoid ambiguity around empty cells, use =COUNTUNIQUE(FILTER(A2:A,A2:A<>"")) instead; both formulas return a count, not a list of the values.

The quickest way to count unique values

Suppose column A contains product names, with a heading in A1 and records below it. In an unused cell, such as C1, enter =COUNTUNIQUE(A2:A). Google Sheets calculates how many different values occur in the range. If the column contains Apple, Pear, Apple, and Plum, the result is 3: the repeated Apple is counted once.

The open-ended range A2:A includes new rows added beneath the current data, so you do not have to update the formula each time the list grows. It also leaves the heading out of the count. If your data begins in another row or column, adjust the reference—for example, use B3:B when values begin in B3. Put the result formula outside the range being counted so the formula does not refer to itself.

COUNTUNIQUE is useful when you need a single total, such as the number of customers in a list or the number of categories represented in a log. It does not remove duplicates or change the source data. The original entries remain as they are.

Exclude blank cells deliberately

For a reliable count of populated entries, filter out empty cells before counting. With values in A2:A, use =COUNTUNIQUE(FILTER(A2:A,A2:A<>"")). The FILTER part keeps rows whose column A value is not an empty string; COUNTUNIQUE then counts the distinct values in that filtered result. If the column contains Red, Blue, Red, and one empty cell, the result is 2.

This approach is especially helpful when a column has many unused rows or formulas that return an empty string. A visually blank cell and a cell containing a formula that returns "" can behave differently in spreadsheet calculations. Testing for A2:A<>"" expresses the intent clearly: do not include values that appear empty.

Be aware that a cell containing a space is not truly empty. It can therefore be counted as a distinct entry even though it looks blank on screen. If the count seems one too high, inspect apparently empty cells for spaces or formulas. You can clean the source data, or use a normalization formula when whitespace needs to be ignored.

Count unique values that meet a condition

A common follow-up is to count distinct entries only for rows matching another column. For example, if A2:A contains customer names and B2:B contains regions, count the nonblank unique customers in East by filtering on both conditions. With customer names in A2:A and regions in B2:B, enter =COUNTUNIQUE(FILTER(A2:A,B2:B="East",A2:A<>"")). If the East rows contain Maya, Lee, Maya, and a blank name, the result is 2.

In Google Sheets, FILTER returns rows that meet every condition supplied to it. Here, one condition selects East and the other excludes empty customer names. To count a different region, replace East with the exact text used in the region column. For a condition stored in a cell, such as D1, use =COUNTUNIQUE(FILTER(A2:A,B2:B=D1,A2:A<>"")) instead. This makes it easier to change the selection without editing the formula.

If there are no rows matching the criteria, FILTER can return a #N/A error because it has no results to return. That is different from a count of zero. You can handle this case with IFNA. With names in A2:A, regions in B2:B, and the selected region in D1, use =IFNA(COUNTUNIQUE(FILTER(A2:A,B2:B=D1,A2:A<>"")),0). This displays 0 when there are no matching entries.

Count only the rows currently shown by a filter

Filtering rows with the Google Sheets toolbar does not automatically make COUNTUNIQUE ignore hidden rows. A formula such as =COUNTUNIQUE(A2:A) still evaluates the values in its referenced range, including rows hidden by a filter view or ordinary filter. This is a frequent source of confusion: the visible table may show only one region while the formula still counts values from every region.

When you want a condition-based result, build that condition into FILTER, as in the region example above. That gives the formula a clear rule to apply regardless of which rows happen to be visible. If the condition depends on several columns, add the corresponding conditions to FILTER. For instance, a date range can be combined with a region selection, provided each condition refers to a range aligned with the values being counted.

If you need a count that changes according to manually hidden rows rather than a stated data condition, COUNTUNIQUE alone is not designed for that job. It is usually more dependable to express the intended selection in the data or formula, rather than rely on which rows happen to be hidden on screen.

Understand what counts as the same value

COUNTUNIQUE counts distinct entries as interpreted by Sheets; it does not automatically standardize inconsistent data. For example, a name with a leading space may be treated as a different entry from the same name without that space. Spelling differences, punctuation, and extra spaces can all make a list look more varied than it really is. Before treating a count as a meaningful business total, check whether the source values have consistent formatting.

Case can also matter to readers even when values look nearly identical. If case differences should not create separate categories, standardize the text before counting. With data in A2:A, one option is to use a helper column to apply TRIM and LOWER to each entry, then count the cleaned results. TRIM removes ordinary extra spaces around text, while LOWER converts letters to lowercase. A helper column makes the transformation visible and easier to review than embedding every cleanup step in one long formula.

TRIM does not remove every character that can look like a regular space. Data pasted from websites or other systems may contain nonbreaking spaces or other hidden characters. If two entries still appear identical but count separately, inspect the source or clean it at import time. It is safer to verify the text than to assume that a formula has corrected every kind of inconsistent input.

Count a unique list or just its total?

COUNTUNIQUE is the shortest choice when you want only a number. If you also need to inspect the distinct entries, use UNIQUE in an empty area of the sheet. With source values in A2:A, enter =UNIQUE(A2:A) to produce a list of unique values. Sheets returns the results as an array, so the cells below the formula need to be available for the list to expand.

To show a list without empty entries, use =UNIQUE(FILTER(A2:A,A2:A<>"")). You can then count the resulting list separately if needed, but for a direct total the COUNTUNIQUE and FILTER combination is simpler. A visible list helps reveal unexpected variations, such as a category with a trailing space, that a single number cannot explain.

Google Sheets and Excel use different function names for this task. COUNTUNIQUE is a Google Sheets function, so it should not be copied into Excel as if it were available there. In Microsoft 365 Excel, a comparable approach uses UNIQUE, FILTER, and ROWS; availability and behavior depend on the Excel version. If you are collaborating across spreadsheet apps, confirm that formulas are supported in the destination rather than assuming they transfer unchanged.

Common errors and how to fix them

The answer is unexpectedly high: Check for spaces, spelling variations, and values that look blank but contain characters. Try the nonblank FILTER version, then inspect the remaining entries.
The answer includes values from other groups: A normal sheet filter only hides rows. Add the desired criteria to FILTER in the formula.
The formula shows #N/A: A FILTER condition may have found no matching rows. Use IFNA around the count if zero is the preferred display for that case.
The formula shows a parse error: Check that quotation marks and parentheses are paired. In locales that use semicolons as formula separators, Sheets may require semicolons instead of commas.
The unique list will not expand: Clear any values in the cells where the UNIQUE results need to appear. An occupied output cell prevents an array result from spilling into that position.

When diagnosing a result, start with a bounded range such as A2:A50 if the open-ended column contains unrelated data far below the list. A bounded reference can make the intended dataset easier to audit, though it must be extended when new records are added. Also confirm that the formula starts below the header and is not placed inside its own input range.

Frequently Asked Questions

Does COUNTUNIQUE count blank cells?
Blank handling can be confusing when a range includes unused cells or formulas returning empty strings. To state clearly that empty-looking entries should be excluded, use =COUNTUNIQUE(FILTER(A2:A,A2:A<>"")) with the data in A2:A. Remember that a cell containing a space is not empty and may still be counted.

How do I count unique values in the whole column?
Start below the heading and use an open-ended reference, such as =COUNTUNIQUE(A2:A). This includes future rows added to column A. If the column contains unrelated notes or a second dataset farther down, use a bounded range that covers only the list you intend to count.

How do I count unique values in Google Sheets with a condition?
Use FILTER to select the qualifying rows and COUNTUNIQUE to count their distinct values. For names in A2:A and regions in B2:B, =COUNTUNIQUE(FILTER(A2:A,B2:B="East",A2:A<>"")) counts nonblank names for East.

Does a sheet filter change the COUNTUNIQUE result?
No. Hiding rows with a filter does not by itself restrict COUNTUNIQUE to the visible rows. Put the selection rule in a FILTER expression if you want the result to count a particular category or group.

How can I display the unique values instead of counting them?
Use UNIQUE, for example =UNIQUE(FILTER(A2:A,A2:A<>"")) for a nonblank list from A2:A. Enter it where the output can expand into empty cells. Use COUNTUNIQUE when you only need the total.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.