Home / Excel Tips / Count Number of Unique Values in a Range (with Excel Functions)

Count Number of Unique Values in a Range (with Excel Functions)

 
Related Links:
Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter
Remove Duplicates or Create a List of Unique Records using Excel Formula
Remove Duplicates in a range, using “Remove duplicates” button in Data Tools
—————————————————————————————————————————
 
Count Number of Unique Values (when No Blank Cells), with Excel Functions:
 
Example 1
 
Table 1a
 
=SUMPRODUCT(1/COUNTIF( A2:A13, A2:A13))  [Formula]
 
The formula returns the count of unique values in the range (A2:A13), which is 6. (Refer Table 1a)
 
Use this formula only if there are no blank cells in the range. In case of any blank cells in the range, formula will return a #DIV/0! Error. A blank cell will return a value of 0 in the COUNTIF function (column C) and its reciprocal ie. division by 1, will return the #DIV/0! error (in column D).
 
Note that total of reciprocals (column D) of every unique item adds up to 1. Adding up all the reciprocals (column D) returns the count of unique values in the range (A2:A13), which is 6.
 
Sumproduct functions as an array formula, you just don’t have to enter it as such.
 
=COUNTIF(A2:A13, A2:A13)  [Formula Break]
 
Returns an array of the individual item counts or occurrences, viz. {2;3;3; 1;2;……}  ->  refer column C in Table 1a.
 
 
Table 1b
=1/COUNTIF(A2:A13,A2:A13)  [Formula Break]
 
Returns an array of reciprocals of the individual item counts or occurrences, viz. {0.50;0.33;0.33; 1.00;0.50; ……}  ->  refer column D in Table 1b.
 
Reciprocals of the item that occurs 2 times will add to 1, of the item that occurs 3 times will also add to 1, and similarly with other items. This means that every unique item returns 1 (column D).
 
—————————————————————————————————————————– 
 
Example 2
{=SUM(1/COUNTIF(A2:A13, A2:A13))}  [Formula]
 
The formula returns the count of unique values in the range (A2:A13), which is 6. (Refer Tables 1a/1b)
 
Use this formula only if there are no blank cells in the range. In case of any blank cells in the range, formula will return a #DIV/0! Error. A blank cell will return a value of 0 in the COUNTIF function (column C) and its reciprocal ie. division by 1, will return the #DIV/0! error (in column D).
 
Enter as an array formula: type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
 
This formula is similar to the above (in Example 1), except that SUM function is substituted for SUMPRODUCT function. Hence, this formula needs to be entered as an array formula, whereas SUMPRODUCT itself functions as an array formula, you just don’t have to enter it as such.
 
 
 
Count Number of Unique Values (for a Range, with or without Blank Cells), with Excel Functions:
 
Example 1
 
Table 2a
 
=SUMPRODUCT(( A2:A14<>””)/COUNTIF( A2:A14, A2:A14&””))  [Formula]
 
The formula returns the count of unique values in the range (A2:A14), which is 5. (Refer Table 2a)
 
Use this formula for a range, with or without blank cells.
 
Note that total of divisions (column E) of every unique item adds up to 1. Adding up all the divisions (column E) returns the count of unique values in the range (A2:A14), which is 5.
 
Sumproduct functions as an array formula, you just don’t have to enter it as such.
 
Blank cells are excluded from the count of unique values.
 
=COUNTIF(A2:A14, A2:A14&””)  [Formula Break]
 
Returns an array of the individual item counts or occurrences, viz. {2;3; 2;1;….}  ->  refer column C in Table 2a.
 
(&””) in the COUNTIF formula, also counts the blank cells as a unique item.
 
 
Table 2b

 

=(A2:A14<>””)  [Formula Break]
 
(A2:A14<>””) returns an array of TRUE (for non-blank cells) and FALSE (for blank cells) values, viz. {TRUE; TRUE;TRUE; TRUE;FALSE;…}  ->  refer column D, in Table 2b.
 
 
Table 2c
 
=(A2:A14<>””)/COUNTIF(A2:A14, A2:A14&””)  [Formula Break]
 
Returns an array of divisions, of TRUE or FLASE divided by the individual individual item counts or occurrences, viz. {0.50; 0.33;0.50; 1.00;0.00;..}  ->  refer column E in Table 2c.
 
In the division, the numerator of TRUE evaluates to 1 for non-blank cells, and the numerator of FALSE evaluates to 0 for blank cells.
 
Divisions for the item that occurs 2 times will add to 1, for the item that occurs 3 times will also add to 1, and similarly with other items (except blank cells) ->  refer column E.
 
Divisions for blank cells will return 0 (the numerator being FALSE evaluating to 0) and will add to 0, thereby excluding them from the count of unique values.
 
This means that every unique item (except blank cell) returns 1, in column E.
 
———————————————————————————————————————– 
 
Example 2
 
{=SUM((A2:A14<>””)/COUNTIF(A2:A14,A2:A14&””))}  [Formula]
 
The formula returns the count of unique values in the range (A2:A14), which is 5. (Refer Tables 2a/2b/2c)
 
Use this formula for a range, with or without blank cells.
 
Enter as an array formula: type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
 
This formula is similar to the above, except that SUM function is substituted for SUMPRODUCT function. Hence, this formula needs to be entered as an array formula, whereas SUMPRODUCT itself functions as an array formula, you only don’t have to enter it as such.
 
———————————————————————————————————————— 
 
Example 3
 
Table 3a
=SUMPRODUCT(–( A2:A14<>””),1/COUNTIF(A2:A14, A2:A14&””))  [Formula]
 
The formula returns the count of unique values in the range (A2:A14), which is 5. (Refer Table 3a)
 
Use this formula for a range, with or without blank cells.
 
Reciprocals are multiplied with 1 (for non-blank cell) or 0 (for blank cells) and then adding the result returns the count of unique values in the range (A2:A14) viz. Column D*Column F = Column G and then added up in Cell G16 (which returns 5).
 
Sumproduct functions as an array formula, you just don’t have to enter it as such.
 
Blank cells are excluded from the count of unique values.
 
 
Table 3b
 
=COUNTIF(A2:A14,A2:A14&””)  [Formula Break]
 
Returns an array of the individual item counts or occurrences, viz. {2;3;2; 1;3;…}, in column C, Table 3b.
 
(&””) in the COUNTIF formula, also counts the blank cells as a unique item.
 
 
Table 3c
 
=1/COUNTIF(A2:A14, A2:A14&””)  [Formula Break]
 
Returns an array of reciprocals of the individual item counts or occurrences, viz. {0.50;0.33; 0.50;1.00;…}  ->  refer column D, Table 3c.
 
Reciprocals of the item that occurs 2 times will add to 1, of the item that occurs 3 times will also add to 1, and similarly with other items (column C).
 
This means that every unique item returns 1  (including blank cells) ->  refer column D.
 
 
Table 3d
 
=(A2:A14<>””)  [Formula Break]
 
(A2:A14<>””) returns an array of TRUE (for non-blank cells) and FALSE (for blank cells) values, viz. {TRUE; TRUE;TRUE; TRUE;FALSE;..}  ->  refer column E, Table 3d.
 
 
Table 3e
=–(A2:A14<>””)  [Formula Break]
 
A non-numeric (BOOLEAN) value is coerced into a numeric value by Excel, by using arithmentic operations  ->  TRUE will become 1 and FALSE will become 0  ->  =TRUE + 2 will give a result of 3, =FALSE + 2 will give a result of 2, =-(TRUE) will give a result of -1, and so on.
 
The first unary minus operator coerces the array to {-1;-1;-1; -1;0;….}. The second unary minus operator negates the array to {1;1; 1;1;0;….}.  This means that –(A2:A14<>””) returns an array of 1 (for non-blank cells) and 0 (for blank cells) values, viz. {1;1;1; 1;0;……}.  ->  refer column F, Table 3e.
 
 
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top