How To Count Unique Values In Column In Excel

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: Where,

is the first cell in the column from which you begin the count. 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: 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: Where,

is the first cell in the column from which you begin the count. 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: 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!