How to Count Unique Values In Excel

Oct 30, 2020 • edited Nov 01, 2020

How to Count Unique Values In Excel

This example shows you how to create an array formula that counts unique values.

1. We use the COUNTIF function. For example, to count the number of 5's, use the following function.

Countif Function

2. To count the unique values (don't be overwhelmed), we add the SUM function, 1/, and replace 5 with A1:A6.

Array Formula

3. Finish by pressing CTRL + SHIFT + ENTER.

Count Unique Values in Excel

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.

Explanation: The range (array constant) created by the COUNTIF function is stored in Excel's memory, not in a range. The array constant looks as follows:

{3;1;1;1;3;3} - (three 7's, one sun, one moon, one 5, three 7's, three 7's)

This reduces to:


This array constant is used as an argument for the SUM function, giving a result of 1/3+1+1+1+1/3+1/3 = 4.

#Tutorial#How To#Functions#Array Formulas

How to Count the Errors In Excel

How to Count with Or Criteria In Excel