Count Unique Values in Excel with a Simple Formula

To count distinct, nonblank entries in a column in Microsoft 365, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) in an empty cell, replacing A2:A100 with your data range. The result is the number of different values, with repeated entries counted once. You need Excel for Microsoft 365 or Excel 2021 or later for the UNIQUE and FILTER functions used here.

What You’ll Need

A workbook containing the values you want to count.
An empty cell for the result. If you use the formula method, the result cell should not be inside the source range.
Excel for Microsoft 365 or Excel 2021 or later for the dynamic-array formula. If you have an older version, see the alternative method and troubleshooting advice below.

Step-by-Step

Identify what should count as one value. Decide which column or range to examine and whether blank cells should be ignored. For example, a list containing Red, Blue, Red, and Green has three unique values. The formula in this guide ignores blank cells, which is usually useful when the list has unused rows or gaps.
Check the range boundaries. Suppose the values are in A2:A100, with a heading in A1. Use A2:A100 as the source range so the heading is not counted as a value. If your list ends at row 250, use A2:A250 instead. You can also select the cells with your mouse and read the range address in the Name Box, to the left of the formula bar.
Select an empty result cell. Click a cell outside the source list, such as C2. Keeping the result separate makes it easier to review and prevents the formula from becoming part of the data it counts.
Enter the count formula. With the values in A2:A100, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) in C2 and press Enter. In this example, A2:A100 is the assumed data layout. FILTER removes blank cells, UNIQUE returns one instance of each remaining value, and ROWS counts how many values that array contains. IFERROR returns 0 if the range has no nonblank values.
Check the result against a small example. If A2:A6 contains North, South, North, East, and a blank, the formula returns 3: North, South, and East. A repeated North contributes only once, and the blank contributes nothing. If you get a different result, confirm that the range includes the intended cells and excludes headings or unrelated notes.
Adjust the formula for your data. Replace both instances of A2:A100 with the correct range. For example, with product names in D5:D60, use =IFERROR(ROWS(UNIQUE(FILTER(D5:D60,D5:D60<>""))),0). The assumed layout for this version is product names in D5:D60; it counts distinct nonblank names in those cells.
Make the formula cover new rows if needed. If people will add entries regularly, convert the list to an Excel Table: select a cell in the data, then choose Insert > Table and confirm whether the table has headers. A table expands as you add rows. If the column is named Item and the table is named Table1, use =IFERROR(ROWS(UNIQUE(FILTER(Table1[Item],Table1[Item]<>""))),0). This assumes the values are in the Item column of Table1. Excel fills table formulas down automatically when entered in a table column, though a separate result cell is convenient for a single total.
Let the result update. The formula recalculates when values in its range change. If you see an old result, check Formulas > Calculation Options and select Automatic. In Excel for Mac, the same calculation setting is available from the Formulas tab. Excel for the web also supports dynamic-array functions in current Microsoft 365 workbooks, though some ribbon locations can differ slightly.
Optionally display the unique list as well. To inspect which values are being counted, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell. This assumes the list is in A2:A100. Excel spills the distinct entries into cells below the formula; make sure those cells are empty. This is a useful check, but you do not need the second formula just to get the count.

The formula counts distinct cell values, not just visibly different formatting. For instance, changing a cell’s font color does not create another value. Dates are stored as numbers, so two dates that look alike may still be different if one contains a hidden time component. Conversely, different number formats can make the same underlying value look different without changing the count.

Common Mistakes

Including the heading. If the range starts at A1 and A1 contains a label such as City, Excel treats that text as a value. Start at the first data row, or deliberately exclude the heading in your selected range.
Using COUNTA as a unique count. COUNTA counts populated cells, including repeated values. A list with five entries and two repeated names does not necessarily have five unique values. Use UNIQUE to remove duplicates before counting.
Counting blanks by accident. UNIQUE by itself can return a blank entry when the source includes blank cells. The FILTER condition in the main formula removes empty cells, including cells whose formulas return an empty string. It does not remove a cell containing spaces.
Missing spaces or inconsistent text. “Lake View” and “Lake View ” may be treated as separate entries because the second value has a trailing space. Likewise, spelling differences such as “St.” and “Saint” remain distinct. Clean the source if those variations should represent one item.
Putting the result inside its own source range. If the formula cell is included in the range it refers to, you can create a circular reference or an unwanted counted value. Put the result elsewhere and keep the input range precise.
Expecting capitalization to create separate values. Excel’s UNIQUE comparison is generally not case-sensitive, so entries such as “Pear” and “PEAR” are treated as duplicates. If capitalization itself matters, the standard formula is not a case-sensitive distinct count.

Tips

Use a Table for changing lists. A fixed range such as A2:A100 will not include entries added below row 100. An Excel Table’s structured reference expands as rows are added, so the count stays current without manually editing the formula.
Use the list formula to audit the answer. The spill list from UNIQUE makes it easier to spot unexpected spaces, spelling variations, or values that should have been excluded. If the spill area is blocked, clear the cells below and beside the formula.
Keep numeric values and text representations consistent. A number stored as numeric 12 and the text entry “12” can behave differently in comparisons and other Excel operations. If a count seems surprising, check whether imported data mixes numbers and text.
Use a PivotTable for a reviewable summary. Select the source data and choose Insert > PivotTable. For a distinct count in a value field, select Add this data to the Data Model when creating the PivotTable, then choose Distinct Count in Value Field Settings. The Data Model option is available in Excel for Microsoft 365 on Windows; availability and interface details can differ on Mac and the web. A PivotTable is handy when you also want counts by category, but it takes more setup than a formula.
Choose a safe alternative for older Excel. If UNIQUE is not recognized, do not keep retrying the same formula: your Excel version may not include dynamic arrays. You can use Data > Advanced to copy unique records to another location, select Unique records only, then count the resulting list. Work on a copy or send the output to an unused area because this method is a manual operation rather than a live formula.

Troubleshooting

You see #NAME?. Excel may not recognize UNIQUE or FILTER because you are using a version earlier than Excel 2021 or a perpetual edition without those functions. Use the Advanced Filter approach above or create a PivotTable instead. If you have Microsoft 365 or Excel 2021 and still see this error, check that the function names are spelled correctly and that you have not pasted curly quotation marks into the formula.
You see #SPILL! when displaying the list. The cells where the UNIQUE results need to appear are not empty, or the spill area is blocked by merged cells. Clear the obstructing cells or move the formula to a larger open area. The count formula using ROWS does not need to display the list in neighboring cells.
You get 0 even though the range looks populated. Check the range references and whether the cells contain errors. The IFERROR wrapper returns zero for errors as well as for an empty result, so it can conceal a problem in the source. Temporarily remove IFERROR to expose the underlying error, then inspect the referenced cells and correct the source or range.
The count is larger than expected. Look for trailing or leading spaces, inconsistent punctuation, hidden time values in dates, and entries that differ only in spelling. To check for spaces, inspect a suspicious cell in the formula bar or use Excel’s TRIM function in a helper column, then count the cleaned values. TRIM removes ordinary extra spaces but does not fix every kind of nonbreaking space imported from websites.
The count does not change after editing. Confirm that the edited cell falls inside the formula’s referenced range. Then check Formulas > Calculation Options > Automatic. If calculation is manual, press F9 on Windows to recalculate; on Mac, use Fn+F9 on keyboards where the function keys control system features, or use the Formulas tab’s calculation command.

Frequently Asked Questions

What is the difference between unique and distinct values?

In everyday Excel questions, “unique” and “distinct” usually mean the number of different values, counting each value once. Some technical uses of “unique” mean values that occur exactly once. The formula in this guide counts distinct values, including values that appear repeatedly, once each.

How do I count unique values in one row instead of one column?

Use the same approach with a horizontal range. For example, with values in B2:J2, enter =IFERROR(COLUMNS(UNIQUE(FILTER(B2:J2,B2:J2<>""))),0). This assumes the values are in B2:J2; COLUMNS counts the returned entries across the row. UNIQUE can return arrays by column for a horizontal input.

Can I count unique values while ignoring blanks?

Yes. The FILTER condition in the main formula excludes blank cells before UNIQUE counts the remaining entries. It also excludes formulas that display an empty string. A cell containing one or more spaces is not truly blank, so remove or clean those spaces if they should be ignored.

Does this formula count repeated values once or only values that appear once?

It counts each distinct value once, even when that value appears many times. For example, a list containing Red, Red, and Blue has two distinct values. It does not count only entries that occur exactly once; that is a different calculation.

Can I count unique combinations across multiple columns?

Yes. UNIQUE can return distinct rows when given a multi-column range, and ROWS can count those rows. For example, with city and product pairs in A2:B100, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:B100,A2:A100<>""))),0). This assumes the first column is populated for each pair. It counts distinct city-product combinations, so the same city paired with two different products counts as two combinations.

Can Excel count values as different when capitalization differs?

Not with the standard UNIQUE formula: its comparisons are not case-sensitive for ordinary text. If “Pear” and “PEAR” must count separately, you need a case-sensitive method, such as a carefully designed helper-column calculation. First confirm that the distinction is genuinely needed, because inconsistent capitalization often indicates data that should be standardized.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.