Easy Excel Formula to Count Unique Values

Do you need to count the number of unique values in an Excel spreadsheet? Whether you’re analyzing survey responses, customer data, inventory, or other information, counting unique values is a common task in Excel. Fortunately, Excel provides an easy formula to count distinct values in a range of cells. In this article, we’ll show you step-by-step how to use the COUNTIF function to count unique values in Excel.

What Does It Mean to Count Unique Values in Excel?

Before we dive into the formula, let’s clarify what we mean by “unique values”. Unique values, also known as distinct values, are values that appear only once in a range of cells.

For example, let’s say you have this list of names in cells A2 to A10 of an Excel worksheet:

In this example, the unique values are:

• John
• Emma
• Liam
• Oliver
• Amelia

Even though “John”, “Emma” and “Liam” are repeated, they each only count once as a unique value. So in total, there are 5 unique names in the list.

Using COUNTIF to Count Unique Values

The most straightforward way to count unique values in Excel is with the COUNTIF function. COUNTIF allows you to specify a range of cells and a criteria, and it will count the number of cells in the range that meet the criteria.

To count unique values with COUNTIF, we’ll use this formula:

=SUM(IF(COUNTIF(range,range)=1,1,0))

Here’s how this formula works:

1. The COUNTIF function counts how many times each value in the range appears in that same range.
2. The IF function checks the result of COUNTIF for each value. If the value appears only once (COUNTIF equals 1), the IF function returns a 1. If the value is repeated (COUNTIF is greater than 1), the IF function returns a 0.
3. Finally, the SUM function adds up the 1’s and 0’s. This gives us the total count of values that appear only once – in other words, the number of unique values.

Step-by-Step Example

Let’s walk through how to use this formula with the list of names from the earlier example.

1. In cell B2, enter the following formula:

   =SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))

2. Press Enter. The formula will calculate and return the result: 5.

The COUNTIF inside the SUM IF formula goes through the list and counts each name. The first time a name appears, COUNTIF equals 1 for that name. If the name is repeated later in the list, COUNTIF will be greater than 1.

The IF statement translates this to 1’s and 0’s – a 1 if the name is unique (COUNTIF=1) or a 0 if it’s a duplicate. SUM then adds up the 1’s to get the total number of unique names.

Handling Blank Cells

One quirk to watch out for – blank cells are counted as a unique value by this formula. If your data has empty cells scattered in it and you don’t want to include them in the unique count, use this modified formula:

=SUM(IF(COUNTIF(range,"<>"&"")*(COUNTIF(range,range)=1),1,0))

This checks that the cell is not blank ("<>"&"") in addition to being a unique value.

Counting Unique Text Values

The COUNTIF approach works perfectly for counting unique text values, like the names example we used.

If you want to count unique text values, but want to treat uppercase and lowercase as the same, you can use the UPPER or LOWER function to standardize the case. Modify the formula like this:

=SUM(IF(COUNTIF(range,UPPER(range))=1,1,0))

Now “John” and “JOHN” would be counted as the same unique value.

Ignoring Leading/Trailing Spaces

Another consideration with text values is leading and trailing spaces. By default, COUNTIF treats “John” and ” John ” as different values because of the extra spaces.

If you want to ignore these spaces and treat both as the same, you can use the TRIM function to remove them. Adjust the formula like this:

=SUM(IF(COUNTIF(range,TRIM(range))=1,1,0))

This will ensure that values differing only by leading/trailing spaces are counted as the same unique value.

Counting Unique Numeric Values

You can also use COUNTIF to count unique numeric values. The formula works the same way as with text.

For example, if you had this data:

Using =SUM(IF(COUNTIF(A2:A8,A2:A8)=1,1,0)) would correctly count 4 unique values.

Treating Numbers as Text

Sometimes your “numbers” might actually be stored as text in Excel. This can happen if the values were imported from a text file or entered with a leading apostrophe (‘). In these cases, COUNTIF will treat “10” and 10 as different values.

If you want to count unique numeric values regardless of whether they’re actual numbers or number-stored-as-text, you can use the VALUE function to convert them all to true numbers. Use this formula:

=SUM(IF(COUNTIF(range,VALUE(range))=1,1,0))

Now COUNTIF will correctly count unique numeric values even if some are stored as text.

Counting Unique Dates

Counting unique dates with COUNTIF can be a little trickier, because Excel stores dates as numbers behind the scenes. If your dates are true Excel dates (not text that looks like dates), COUNTIF will work. But if you have a mix of real dates and text-that-looks-like-dates, you may get unexpected results.

To handle this, you can use the DATEVALUE function to convert all the dates to a consistent numeric representation before counting. Use a formula like this:

=SUM(IF(COUNTIF(range,DATEVALUE(range))=1,1,0))

This will properly count unique dates whether they are real Excel dates or text dates.

Counting Unique Date Components

Sometimes you might want to count unique dates based on just one component of the date, like the year, month, or day.

You can extract these components with functions like YEAR, MONTH, and DAY. For example, to count unique years, you could use:

=SUM(IF(COUNTIF(range,YEAR(range))=1,1,0))

This would count “1/1/2023” and “12/31/2023” as the same unique value because they’re in the same year.

You can similarly use MONTH or DAY to count unique months or unique days across a range of dates.

Final Thoughts

The COUNTIF function in Excel provides an easy way to count the number of unique values in a range of cells. By combining COUNTIF with SUM and IF, you can build a formula that counts each distinct value only once, whether you’re working with text, numbers, or dates.

Remember these key points when counting unique values in Excel:

• COUNTIF counts blank cells as a unique value by default. Use "<>"&"" to exclude blanks.
• For text values, case matters. Use UPPER or LOWER to ignore case.
• Leading/trailing spaces matter. Use TRIM to ignore them.
• For numbers stored as text, use VALUE to convert them.
• For dates, use DATEVALUE to handle text-dates.
• You can count unique date parts with YEAR, MONTH, or DAY.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.