Count Unique Values in Excel

by

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

Examples:

1.. To count the number of 5’s, use the following function.

We use the COUNTIF function.

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

3. Finish by pressing CTRL + SHIFT + ENTER.

Worked Example:   COUNTIF with non-contiguous range 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.

Worked Example:   Highlight unique values in Excel

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:

Worked Example:   Count cells less than in Excel

{3;1;1;1;3;3} – (three 7’s, one sun, one moon, one 5, three 7’s, three 7’s)

This reduces to:
{1/3;1/1;1/1;1/1;1/3;1/3}

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.

Leave a Reply

Your email address will not be published.