Count Unique in Google Sheets: Formulas and Examples

To count distinct values in Google Sheets, use =COUNTUNIQUE(A2:A100) when the values are in cells A2 through A100. The function returns one number: how many different values occur in that range, counting repeats only once.

What COUNTUNIQUE counts

COUNTUNIQUE is a Google Sheets function for counting distinct entries. If a list contains “apple,” “pear,” “apple,” and “plum,” the result is 3: the second appearance of “apple” does not add to the count. This is different from counting every filled cell, which would return 4 for that example.

The function works with a range of cells or with individual values. For a changing list, referencing a range is usually more useful than typing each value into the formula, because new entries can be included by extending the range or using a whole-column reference. A whole-column reference can be convenient, but it may make a large spreadsheet calculate more slowly than a range sized to the data.

“Unique” here means distinct, not necessarily appearing exactly once. A value repeated several times still contributes one to the count. If the question is instead “How many values occur only one time?”, that is a different calculation; COUNTUNIQUE alone does not answer it.

Basic formula and range choices

Suppose product names are in A2:A100, with a heading in A1. Enter the formula in an empty cell outside the list:

With product names in A2:A100, use =COUNTUNIQUE(A2:A100). The formula returns the number of distinct entries in those 99 cells.

Use the actual data range rather than including a heading. If a heading such as “Product” is inside the range, it is a value too and may increase the result by one. When records will be added regularly, you can reference the entire column:

With product names anywhere in column A, use =COUNTUNIQUE(A:A). This counts distinct values in that column, including any heading or notes there, so choose a clean data column.

For a smaller, fixed data set, a bounded range such as A2:A500 makes it clear which rows are included. If the list grows beyond row 500, update the formula range. Keep the result cell outside the range it counts; otherwise, the formula can include itself and produce a circular dependency.

Examples with names, IDs, and dates

Consider a sign-up sheet with one attendee per row in B2:B50. If the same person registered twice under exactly the same name, COUNTUNIQUE counts that name once. This can help answer “How many different names are listed?” It does not prove how many actual people attended: spelling differences, nicknames, or two people sharing a name can affect the result. An ID field is often more dependable when one is available.

For order tracking, a column of order IDs can be counted the same way. The result is the number of different IDs in the selected range, not the number of orders represented by every row. If each row is already one order, counting rows and counting distinct IDs may give different answers when the sheet contains duplicate records.

Dates can also be counted as distinct values. Spreadsheet dates are stored as numeric values and displayed using date formats, so two cells showing the same calendar date normally represent the same date value. However, a date-time column may contain different times on the same day. Those are different underlying values even if the cell format displays only the date. If the intended question is “How many calendar days?”, first use a date-only helper column or otherwise remove the time component, then count the distinct dates.

Likewise, a number stored as text may not be treated the same as the numeric value. This is common with imported IDs, postal codes, and codes that begin with zero. Decide whether formatting differences matter before changing data: converting an ID such as 0042 to the number 42 may lose meaningful leading zeros.

Blanks, spaces, and inconsistent entries

Empty cells, formulas that display an empty string, and cells containing spaces are not always equivalent. In particular, a cell that looks blank may contain a formula or invisible whitespace. If the goal is to count distinct nonblank values only, explicitly filter out entries that compare as empty.

With values in A2:A100, use =COUNTUNIQUE(FILTER(A2:A100,A2:A100<>"")). This counts the distinct entries remaining after FILTER removes cells that are empty or evaluate to an empty string. If no nonblank entries remain, FILTER can return an error; handle that case if an empty list is possible.

With values in A2:A100 and the possibility that every cell is blank, use =IFERROR(COUNTUNIQUE(FILTER(A2:A100,A2:A100<>"")),0). This returns 0 when there are no qualifying values. The IFERROR wrapper is intended for that no-results case; when adapting it, be aware that it can also hide other errors in the wrapped expression.

Trailing spaces can create entries that look identical but are stored differently, such as “North” and “North ”. To inspect or clean a column, select the relevant cells and choose Data > Data cleanup > Trim whitespace in Google Sheets. Review the data before and after cleanup, especially if spaces could be intentional. Standardizing capitalization or spelling may also be necessary when those differences should not produce separate categories.

Count distinct values that meet a condition

A common follow-up is to count unique entries only for rows that match a category. For example, suppose product names are in A2:A100 and regions are in B2:B100. To count products listed for the East region, filter the product range to matching rows and count the results.

With products in A2:A100 and regions in B2:B100, use =COUNTUNIQUE(FILTER(A2:A100,B2:B100="East")). It counts each matching product once. Make sure both ranges cover the same rows so that each product is tested against its corresponding region.

If there might be no rows for the requested region, FILTER may return an error because there is nothing to count. With the same product and region layout, use =IFERROR(COUNTUNIQUE(FILTER(A2:A100,B2:B100="East")),0) to display 0 for that situation. Replace “East” with the category you need, or refer to a cell containing the category so the criterion can be changed without editing the formula.

For more complex criteria, make the filtering logic explicit and check that every condition refers to the intended rows. COUNTUNIQUE counts the values supplied to it; FILTER determines which values are supplied. Separating those jobs makes it easier to troubleshoot than trying to interpret a complicated result as if it were a simple total.

COUNTUNIQUE versus UNIQUE

COUNTUNIQUE returns a single count. The separate Google Sheets function UNIQUE returns a list of distinct values in the sheet. Choose COUNTUNIQUE when the answer you need is a number, such as the number of different departments. Choose UNIQUE when you need to see the distinct department names themselves, perhaps to create a review list.

For example, with departments in A2:A100, entering =UNIQUE(A2:A100) in an empty area produces a list of distinct department entries. The output expands into cells below the formula, so leave that area clear. If cells in the output area are occupied, the result may be blocked. The list can be useful for checking misspellings or unexpected categories before reporting a count.

These functions do not answer the “appears exactly once” question by themselves. A value that appears two or more times is still present in the UNIQUE output and contributes one to COUNTUNIQUE. If you need a count of entries that occur once, identify frequencies first and then count only those with a frequency of one.

Common mistakes and troubleshooting

Including a header: Start below the heading, or the heading can be counted as another distinct value.
Counting the wrong field: Count IDs when the question is about distinct records, rather than names that may be inconsistent.
Expecting unique rows: COUNTUNIQUE on one column counts distinct cell values, not distinct combinations across several columns.
Hidden differences: Spaces, alternate spellings, text-versus-number values, and date-time values can make apparently similar entries distinct.
Using a changing range: If new records are added outside a bounded range, the formula will not include them until you expand the range.
Putting the formula inside its input range: Place the result elsewhere to avoid a circular reference.

When a number seems unexpectedly high, use UNIQUE in a blank area to inspect the values being counted. That often reveals a stray space, an old category, or a value that differs only in its underlying data. When it seems low, confirm that the source range includes all the intended rows and that the criterion in a filtered calculation matches the data exactly.

Google Sheets and Excel differences

This guidance is for Google Sheets in a web browser. COUNTUNIQUE is a Google Sheets function and is not the function name used by Excel. Current Microsoft 365 versions of Excel provide the dynamic-array function UNIQUE, which can be used as part of a different approach to counting distinct values. Do not paste a Sheets formula into Excel and assume it will work unchanged.

Menu locations and available functions differ between the products, too. For Google Sheets cleanup, use its own Data menu rather than following Excel instructions. If a spreadsheet is shared or exported between Sheets and Excel, verify formulas and results in the destination application, particularly if the file uses filters, array results, or functions specific to one product.

Frequently Asked Questions

How do I count unique values in Google Sheets?
Use COUNTUNIQUE with the range, for example =COUNTUNIQUE(A2:A100) for values in A2:A100. It returns the number of distinct values in that range.

Does COUNTUNIQUE count duplicates more than once?
No. Repeated instances of the same value contribute one to the distinct count. The function counts different values, not the total number of populated rows.

How can I count unique values while ignoring blanks?
Filter out empty entries before counting. For data in A2:A100, use =IFERROR(COUNTUNIQUE(FILTER(A2:A100,A2:A100<>"")),0) if the range could contain no nonblank values.

Can I count unique values that match a condition?
Yes. Use FILTER to keep matching rows, then count the resulting values with COUNTUNIQUE. For example, filter a product column by its corresponding region column, making sure both ranges start and end on the same rows.

What is the difference between COUNTUNIQUE and UNIQUE?
COUNTUNIQUE returns a number; UNIQUE returns a list of distinct values. Use the first for a total and the second when you need to inspect or reuse the actual entries.

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.