You are currently viewing Different Ways in Excel How to Count Unique Values in a Column

Different Ways in Excel How to Count Unique Values in a Column

  • Post author:
  • Post category:Formula

Microsoft Excel has helped people and businesses in solving the hardest tasks every day by making the work less tedious and very easy for them. It is a spreadsheet in the Microsoft Office suite of applications that individuals and businesses are using for their everyday operations. It's very easy to manipulate spreadsheet tables of values using formulas and operations. It can be used in many ways but today we have to focus on one of them. The main focus is on how to count unique values in a column using different ways in excel. The following are some of the methods.


Use the advanced filter to calculate unique values.

One of the easiest ways of counting unique values is the use of advanced filters. At this point, we are going to give steps that you are supposed to follow in this case.

Start by selecting any cell from the list.

  • Now head to data tab- sort and filter> Advanced.
  • A pop-up window will come up prompting you to apply advanced filters.
  • Now from this window, you will have to copy to another location".
  • You are supposed to select a blank cell in Copy to so that you will have a way of posting the unique values
  • Now, go to the mark unique values and eventually choose OK
  • You will now have a list of unique values.
  • Past the last cell of the list, choose another cell and put the below formula: =COUNTA (B2:B10) to return the count of unique values from the list.

    At last, you have the count of unique values and the list.

This method is easy and straightforward to follow, as you don't need to write complex formulas for this.


Sum and COUNTIF formula

This is another simple formula to help you count unique values in a single cell. It will also help you avoid extracting separate lists

You will only have to refer to the list of values, and the eventual results will be excellent.

The first thing to do is to enter this formula as an array. To do this, press, Ctrl+Shift+Enter.

After this, incorporate the below formula:

=SUM(1/COUNTIF(A2:A17, A2:A17)

The resulting formula will look like{=SUM(1/COUNTIF(A2:A17, A2:A17)}

How it works.

The formula works in a very easy way. However, you should break it into three parts to understand it better. You also have to remember that the formula is entered as an array. The last thing to bear in mind is that there are 16 total values in the list.

Now, in the first part, you have used COUNIF to count the number of each value from 16, and here COUNTIF returns the values like below.

The second part of this shows that you have divided the values by 1, which will give you the below results.

IMPORTANT POINT: If any of the values is in the list twice, you will get a return of 0.5 for the two values. This means that in the end, the total will be 1. Similarly, if a value is there three times, then you will get a return of 0.333 so that after adding, you will still get 1.

In the other part, you will have to use the SUM to sum all those values. This will give you the count of the values.

This formula is sophisticated and can help you get the count in a single cell.



Assume you have a list of names and their specific phone numbers. If in this case, you want to count unique values just from the text values, you have to utilize the formula below.

=SUM (IF (ISTEXT (A2:A17), 1/COUNTIF (A2:A17, A2:A17),""))

And when you enter this formula as an array.

{=SUM (IF (ISTEXT (A2:A17), 1/COUNTIF (A2:A17, A2:A17),""))}

How does this work

Note the use of ISTEXT and IF function. The first confirms that all the values are either values or not while the later will appear true if a value is a text.

ISTEXT first confirms that all the values are text or not and return TRUE if a value is a text.

In the instances where you have True and other values, If applies COUNTIF.

Lastly, the sum returns the addition of all the unique values. These in simple terms are texts, and you will eventually get the count of the unique values.