Sorting out the number of unique and distinct values from a list across a column in an Excel sheet could be useful for many purposes. You might need to calculate the number of players who won a single event (or multiple) or the number of unique items sold to a customer (or distinct). In this situation, such sorting is very helpful.
How to count unique values in column in Excel
Count the number of unique values from a list of a column using the array formula
The syntax for counting the number of unique values from a list of a column using the array formula is as follows:
=SUM(IF(COUNTIF(<first cell from which you count the number of unique values>:<last cell till which you count the number of unique values>,<first cell from which you count the number of unique values>:<last cell till which you count the number of unique values>)=1,1,0))
Where,
- <first cell from which you count the number of unique values> is the first cell in the column from which you begin the count.
- <last cell till which you count the number of unique values> is the last cell in the column till which you count.
Eg. Let us say we have a list of sports and the players who won at each sport. The winning players are listed from cell B3 to B12. The formula for unique values of the players would become:
=SUM(IF(COUNTIF(B3:B12,B3:B12)=1,1,0))
Place this formula in the cell where you need the number of unique values (say cell D3) and press CTRL+SHIFT+ENTER. This will enable the array formula. Now click anywhere outside the cell and you will get the required number of unique values.
Count the number of distinct values from a list of a column using the array formula
The syntax for counting the number of distinct values from a list of a column using the array formula is as follows:
=SUM(IF(<first cell from which you count the number of distinct values>:<last cell till which you count the number of distinct values><>"",1/COUNTIF(<first cell from which you count the number of distinct values>:<last cell till which you count the number of distinct values>,<first cell from which you count the number of distinct values>:<last cell till which you count the number of distinct values>), 0))
Where,
- <first cell from which you count the number of distinct values> is the first cell in the column from which you begin the count.
- <last cell till which you count the number of distinct values> is the last cell in the column till which you count.
Eg. Assume a case where we have a list of sports and the players who won at each sport. The winning players are listed from cell B3 to B12. The formula for distinct values of the players would become:
=SUM(IF(B3:B12<>"",1/COUNTIF(B3:B12, B3:B12), 0))
Place this formula in the cell where you need the number of distinct values (say cell E3) and press CTRL+SHIFT+ENTER. This will enable the array formula.
Now click anywhere outside the cell and you will get the required number of distinct values.
Hope this helps!
