Home ➜ Excel Formulas ➜ How to Count Unique Values in Excel [Formulas + VBA]
Let’s say you have a list of values where each value is entered more than once.
And now…
You want to count unique values from that list so that you can get the actual numbers of values that are there.
For this, you need to use a method that will count value only one time and ignore it’s all the other occurrences in the list.
In Excel, you can use different methods to get a count of unique values. It depends that which type of values you have so that you can use the best method for it.
In today’s post, I’d like to share with you 6 different methods to count unique values and use these methods according to the type of values you have.
Advanced Filter to Get a Count of Unique Values
Using an advanced filter is one of the easiest ways to check the count of unique values and you don’t even need complex formulas.
Here we have a list of names and from this list, you need to count the number of unique names.
Following are the steps you need to follow to get the unique values:
- First of all, select any of the cells from the list.
- After that, go to Data Tab ➜ Sort & Filter ➜ Click on Advanced.
- Once you click on it, you will get a pop-up window to apply advanced filters.
- Now from this window, select “Copy to another location”.
- In “Copy to”, select a blank cell where you want to paste unique values.
- Now, tick mark “Unique Records Only” and click OK.
- At this point, you have a list of unique values.
- Now, go to the cell below the last cell of the list and insert the following formula and hit enter.
=COUNTA(B2:B10)
It will return the count of unique values from that list of names.
Now you have a list of unique values and count as well. This method is simple and easy to follow as you don’t need to write complex formulas for this.
Combination of SUM and COUNTIF to Count Unique Values
If you want to find the count of unique values in a single cell without extracting a separate list, then you can use a combination of SUM and COUNIF.
In this method, you just have to refer to the list of the values and the formula will return the number of unique values.
This is an array formula, so you need to enter it as an array, and while entering it use Ctrl + Shift + Enter.
And the formula is:
=SUM(1/COUNTIF(A2:A17,A2:A17))
When you enter this formula as an array it will look something like this.
{=SUM(1/COUNTIF(A2:A17,A2:A17))}
How it Works
To understand this formula you need to break it down into three parts and just remember that we have entered this formula as an array and there are total of 16 values in this list, not unique but total.
Ok, so look.
In the first part, you have used COUNIF to count the number of each value from 16 and here COUNTIF returns values like below.
In the second part, you have divided all the values with 1 which returns value like this.
Let’s say if a value is there in the list twice, then it will return 0.5 for both of the values so that in the end when you sum it, it becomes 1 and if a value is there for three times it will return 0.333 for each.
And, in the third part, you have simply used the SUM function to sum all those values and you have a count of unique values.
This formula is quite powerful and it can help you to get the count in a single cell.
Use SUMPRODUCT + COUNTIF to Get a Number of Unique Values from a List
In the last method, you have used the SUM and COUNTIF methods. But, you can also use SUMPRODUCT instead of SUM.
And, when you use SUMPRODUCT, you don’t need to enter a formula as an array. The formula you need to enter is,
=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))
When you enter this formula as an array it will look something like this.
{=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))}
How it works
This formula exactly works in the same way as you have learned in the above method, the difference is just that you have used SUMPRODUCT instead of SUM.
And SUMPRODUCT can take an array without using Ctrl + Shift + Enter.
Count Only Unique Text Values from a List
Now, let’s say you have a list of names in which you also have mobile numbers and you want to count unique values just from text values.
So, in this case, you can use the below formula:
=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 it Works
In this method, you have used the IF function and ISTEXT. ISTEXT first verifies whether all the values are text or not and return TRUE if a value is a text.
After that, IF applies COUNTIF on all the text values where you have TRUE and other values remain blank.
And in the end, SUM returns the sum of all the unique values which are text and you get the count of unique text values this way.
Get Count of Unique Numbers from a List
And if you just want to count unique numbers from a list of values then you can use the below formula.
=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))
Enter this formula as an array.
{=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}
How it works
In this method, you have used the IF function and ISNUMBER. ISNUMBER first verifies that all the values are numeric or not and return TRUE if a value is a number.
After that, IF applies COUNTIF on all the numeric values where you have TRUE and other values remain blank.
And in the end, SUM returns the sum of all the unique values which are numbers and you get the count of unique numbers this way.
Count Unique Values with a UDF
Here I have VBA (UDF) which can help you to count unique values without using any kind of formula.
Function CountUnique(ListRange As Range) As Integer
Dim CellValue As Variant
Dim UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellValue In ListRange
UniqueValues.Add CellValue, CStr(CellValue) ' add the unique item
Next
CountUnique = UniqueValues.Count
End Function
Enter this function in your VBE by inserting a new module and after that go to your worksheet and insert the following formula.
=CountUnique(range)
Conclusion
Counting unique values can be useful for you while working with large datasets.
Like the name list which you have used here had duplicate names and after calculating unique numbers, we get that there are 10 unique names in the list.
Well, all the methods which you have learned here are useful in different situations and you can use anyone from those which you think is a perfect fit for you.
If you ask me, advanced filter and SUMPRODUCT is my favorite methods, but now you need to tell me:
Which one is your favorite?
Please share your views with me in the comment section, I’d love to hear from you, and don’t forget to share this tip with your friends.
Thanks for the formula and tutorial.
Hi Puneet
I want to thank you for the excellent function, to count unique values, you shared with us.
Jorge
Using the #6 VBA solution, Is there a way to count unique values in a range such that the comparison is case sensitive? So a range containing asdfg2X and asdfg2x would not consider these to be the same?
After looking at many VBA codes from many help web sites this is the simplest and only one that actually worked to just count the number of unique entries in a column. A single worksheet line that beats those long complicated VBA codes.
=SUMPRODUCT(1/COUNTIF(E2:E137,E2:E137))
Not sure about the {} array stuff didn’t get any joy from that.
I like the well worded explanations on the workings of the codes.
Lets start with the first example which, for me, goes half way. You show a lista of all the discreet names but not how many times they appear, so Chi appears twice and I’d like to know how many times the others appear. Simply how many times does each individual make an appearance.
I prefer to use =COUNTA(UNIQUE(A3:A18)). Of course, this works with Google Sheet. since, I have only excel 2007, I am unable to try this function. For extracting only text values, I used, =ArrayFormula(if(istext(K3:K22),UNIQUE(K3:K22),””)) and for Numerical value I used =ArrayFormula(if(isnumber(K3:K22),UNIQUE(K3:K22),””)). Of course, my testing is with Google Sheets.
Is there a way to count unique values with a specific character from a list. IE count unique where a2:a15 contain the letter “I”
Thank you for this coding
Function CountUnique(ListRange As Range) As Integer
Dim CellValue As Variant
Dim UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellValue In ListRange
UniqueValues.Add CellValue, CStr(CellValue) ‘ add the unique item
Next
CountUnique = UniqueValues.Count
End Function
Is there any can we add multiple criteria unique values
for unqiue value a1:a56, b1:b6,”sales person”
Hi Puneet,
Great stuff, thanks a lot! What I am also interested in is unique values for filtered ranges, especially if they contain blanks. And that for three cases: strings only, numbers only and finally mixed ranges. Any help on that would be great.
Thanks in advance,
Rob
RE: 5. Get Count of Unique Numbers from a List
And if you just want to count unique numbers from a list of values then you can use below formula.
=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))
The change below works for me in Excel 2016 by deleting the double quotes at the end after the comma. The above causes Excel to return error #NAME? …
Mod below does not require CSE array entry…hope this helps someone! However, when adding the “” back into the formula, it functioned Ok. Go figure. Try both until one works for you.
=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),))
VBA is the best
SUM PRODUCT AND SUM ARE NOT WORKING IN THE LIST AND EXCEL IS NOT TAKING CTRL+SHIFT+ENTER
I use another way, copy paste list to other sheet and remove duplicates.
Yes, that’s simple. Only thing, this is not dynamic but fine if you need to do this once.
And there is another way to obtain a list of unique values: design a Pivot Table with names in the rows and Count Of Names in Values area.
Sincerely yours,
Carlos
Thanks Carlos, I just forgot to share this one.
Advanced filter is the best.