by The tutorial looks at how to leverage the new dynamic array functions to count unique values in Excel: formula to count unique entries in a column, with multiple criteria, ignoring blanks, and more.
A couple of years ago, we discussed various ways to count unique and distinct values in Excel. But like any other software program, Microsoft Excel continuously evolves, and new features appear with almost every release. Today, we will look at how counting unique values in Excel can be done with the recently introduced dynamic array functions. If you have not used any of these functions yet, you will be amazed to see how much simpler the formulas become in terms of building and convenience to use.
Note. All the formulas discussed in this tutorial rely on the UNIQUE function, which is only available in Excel 365 and Excel 2021. If you are using Excel 2019, Excel 2016 or earlier, please check out this article for solutions.
Count unique values in column
The easiest way to count unique values in a column is to use the UNIQUE function together with the COUNTA function:
The formula works with this simple logic: UNIQUE returns an array of unique entries, and COUNTA counts all the elements of the array.
As an example, let's count unique names in the range B2:B10:
=COUNTA(UNIQUE(B2:B10))
The formula tells us that there are 5 different names in the winners list:
Tip. In this example, we count unique text values, but you can use this formula for other data types too including numbers, dates, times, etc.
Count unique values that occur just once
In the previous example, we counted all the different (distinct) entries in a column. This time, we want to know the number of unique records that occur only once. To have it done, build your formula in this way:
To get a list of one-time occurrences, set the 3rd argument of UNIQUE to TRUE:
UNIQUE(B2:B10,,TRUE))
To count the unique one-time occurrences, nest UNIQUE in the ROW function:
ROWS(UNIQUE(B2:B10,,TRUE))
Please note that COUNTA won't work in this case because it counts all non-blank cells, including error values. So, if no results are found, UNIQUE would return an error, and COUNTA would count it as 1, which is wrong!
To handle possible errors, wrap the IFERROR function around your formula and instruct it to output 0 if any error occurs:
=IFERROR(ROWS(UNIQUE(B2:B10,,TRUE)), 0)
As the result, you get a count based on the database concept of unique:
Count unique rows in Excel
Now that you know how to count unique cells in a column, any idea on how to find the number of unique rows?
Here's the solution:
The trick is to "feed" the entire range to UNIQUE so that it finds the unique combinations of values in multiple columns. After that, you simply enclose the formula in the ROWS function to calculate the number of rows.
For example, to count the unique rows in the range A2:C10, we use this formula:
=ROWS(UNIQUE(A2:C10))
Count unique entries ignoring blank cells
To count unique values in Excel ignoring blanks, employ the FILTER function to filter out empty cells, and then warp it in the already familiar COUNTA UNIQUE formula:
With the source data in B2:B11, the formula takes this form:
=COUNTA(UNIQUE(FILTER(B2:B11, B2:B11<>"")))
The screenshot below shows the result:
Count unique values with criteria
To extract unique values based on certain criteria, you again use the UNIQUE and FILTER functions together as explained in this example. And then, you use the ROWS function to count unique entries and IFERROR to trap all kinds of errors and replace them with 0:
For example, to find how many different winners there are in a specific sport, use this formula:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10=E1))), 0)
Where A2:A10 is a range to search for unique names (range), B2:B10 are the sports in which the winners compete (criteria_range), and E1 is the sport of interest (criteria).
Count unique values with multiple criteria
The formula for counting unique values based on multiple criteria is pretty much similar to the above example, though the criteria are constructed a bit differently:
Those who are curious to know the inner mechanics, can find the explanation of the formula's logic here: Find unique values based on multiple criteria.
In this example, we are going to find out how many different winners there are in a specific sport in F1 (criteria 1) and under the age in F2 (criteria 2). For this, we are using this formula:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<F2)))), 0)
Where A2:B10 is the list of names (range), C2:C10 are sports (criteria_range 1) and D2:D10 are ages (criteria_range 2).
That's how to count unique values in Excel with the new dynamic array functions. I am sure you appreciate how much simpler all the solutions become. Anyway, thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Count unique values formula examples (.xlsx file)
217 comments
Rather than repeat my "UNIQUE()" call, I want to do the following:
=UNIQUE(range) <-- in cell B5
=ROWS(B5)
or =COUNTA(B5)
etc.
Hello Wayne!
Please re-check the article above since it covers your task. If the suggested formulas doesn't work for your case, feel free to describe it in detail.
Here is one of the formulas: COUNTA(UNIQUE(range))
COUNTA function that references a single cell with a formula will always return 1.
I think the following is meant to read 'Where A2:A10 is the list of names (range), B2:B10 are sports (criteria_range 1) and C2:C10 are ages (criteria_range 2)' - ?
> Where A2:B10 is the list of names (range), C2:C10 are sports (criteria_range 1) and D2:D10 are ages (criteria_range 2).
Hi there is is possible to sum multiple UNIQUE logic statements in a single formulae? I imagine yes, but I can't figure it out!
My data is relating to shifts and callouts.
Col G Col M
Date Day
24/7/24 Wed
22/7/24 Mon
18/7/24 Thu
16/7/24 Tue
15/7/24 Mon
6/7/24 Sat
6/7/24 Sat
6/7/24 Sat
6/7/24 Sat
6/7/24 Sat
3/7/24 Wed
2/7/24 Tue
I am trying to do a couple of things here.
1. Count the number of shifts on weekdays / sat / sun - for unique days of the week. E.g. all the calls logged on 6/7/24 actually only equate to a single shift - i am using the Unique function to do this. The formula I have is =IFERROR(ROWS(UNIQUE(FILTER(G2:G13, M2:M13="Sat"))), 0) and it works - returning a value of 1 as expected.
2. I am trying to perform the same function but for weekdays only - e.g. I want a summation of all of the unique shifts worked on weekdays - I tried =IFERROR(ROWS(UNIQUE(FILTER(G2:G13, (M2:M13="Mon")*(M2:M13="Tue")*(M2:M13="Wed")*(M2:M13="Thu")*(M2:M13="Fri")))), 0) but this returned a value of 0, when I was expecting a value of 7. I am guessing I have made the logic test too complicated somehow?
Hello Julia!
I recommend using the WEEKDAY function instead of text to determine the day of the week.
Based on the information provided, the formula could look like this:
=IFERROR(ROWS(UNIQUE(FILTER(G2:G13, WEEKDAY(G2:G13,2)<6))), 0)
Thanks! Worked perfectly! Just for my understanding, what is the significance of the 2 and the <6 in the weekday portion of the function?
WEEKDAY function returns the number of the day of the week. Read carefully the instructions I recommended to you.
Hi, please help me to resolve. I have used the formula countif(G:G,G2) and pulled down to count unique values in G column.
Now, in column A, I have another set of unique values. How do I count uniques in column G per each unique value in column A?
Thank you,
Olga
Hi! Your formula counts how many times a value occurs in column.
You can get list of unique values in a column by using UNIQUE function.
If you have a list of unique values in column G and a list of unique values in column A, you can compare these columns and count matches as described in these articles: How to compare two columns in Excel for matches and differences and VLOOKUP to compare two columns in Excel for common values and missing data.
I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
Good afternoon,
I am trying to put together a dashboard that counts specific unique numbers in a column that starts with the number "13" but doesn't count duplicates.
For example
Column A "Customer ID"
Row 2: 13xxxxxx
Row 3: 13xxxxxx
Row 4: 25xxxxx
In the column I have Customer ID's that start with a 13 and others that start with a 25. I am trying to come up with a formula to only count the ones that start with a "13" but do not count the duplicates listed as "13"
Is this possible? Thank you for your help on this.
Hi! Use the LEFT function to extract the first 2 digits and compare them to "13". Use this as a condition in the FILTER function. Follow the recommendations in the article above. For example:
=IFERROR(ROWS(UNIQUE(FILTER(A1:A10,LEFT(A1:A10,2)="13",""))),0)
How to find the no.of employees without an unwanted name?
Hi! From the total number of employees, subtract the number of employees with the unwanted name. You can use the COUNTIFS function to do this.
Hello,
I'm trying to count the number of names in column Child Care I5:I27 without counting duplicates, if column Child Care F5:F27 has "January" selected.
So far I have this to count the number of names without counting duplicates, but I can't figure out how to get the second condition to work.
=COUNTA(UNIQUE(FILTER('Child Care'!I5:I27,'Child Care'!I5:I27"")))
Thank you.
Hi! Pay attention to the following paragraph of the article above: Count unique values with multiple criteria. You can also see instructions on how to use the FILTER function with multiple criteria: Filter with multiple AND criteria. Based on the information given, the formula could be as follows:
=COUNTA(UNIQUE(FILTER(I5:I27,(I5:I27<>"")*(MONTH(F5:F27)=1))))
Hi, I tried the formula and it's still only counting the number of unique names in column I5:I27. The month column is F5:F27.
If the lead column I is selected with Brenda, Rachel and Cammie and January is selected for each of the names I want the count at 3.
If Brenda is selected and January is also selected more than once I don't want it to be counted again, which is why I was trying to use the =COUNTA(UNIQUE(FILTER(I5:I27,(I5:I27""). This part of the formula works perfectly.
It's when I add the other criteria of only counting if the given month "January" is selected, then I'll use it for "February" and so on.
Do you have any other suggestions?
Hi! I can't see your data. But it's not hard to guess that if the column F contains not the date but the name of the month, you need to use the condition (F5:F27="January"). If I'm not guessing, give me an example of your data.
Thank you so much! You are correct column F contains the name of the month, so this formula now works beautiful! :)
=COUNTA(UNIQUE(FILTER('Child Care'!I5:I27,('Child Care'!I5:I27"")*('Child Care'!F5:F27="January"))))
Looking at the formula again as I'm trying to set up a spreadsheet for 2024.
When I use the following formula for months that have already passed it works great since the month has been selected, however, when looking at future months the formula gives a return of 1 rather than 0.
=COUNTA(UNIQUE(FILTER('Child Care'!I5:I27,('Child Care'!I5:I27"")*('Child Care'!F5:F27="May"))))
Can you please help with using this?
Hi! If none of the values in the column match the conditions in the FILTER function, an error will be returned. This occurs even if the column is empty. Use the ISERROR function to catch these errors. Use the IF function to specify that the calculation is only performed if there is no error, i.e., if there are some values in the month column. Here's an example of a formula you can apply to your data:
=IF(ISERROR(UNIQUE(FILTER(B2:B11,B2:B11<>""))), "", COUNTA(UNIQUE(FILTER(B2:B11,B2:B11<>""))))
Hello,
I wasn't able to get the IF(ISERROR) to work in the formula. I would like to send you a copy of the data I'm working with if that's possible? Where can I send it?
Just to recap the goal for me is to count employee names, without duplicates and without blank lines, then if an employee name is listed and the month "January", "February", "March" and so on is listed the answer will show in the January column how many employee names were listed.
The issue is when an employee name is listed, but the month is not listed such as "March" I am getting a 1, but it should be 0.
The formula =COUNTA(UNIQUE(FILTER('Child Care'!I3:I27,('Child Care'!I3:I27"")*('Child Care'!F3:F27="January")))) works great as long as an employee name is listed with the month is listed at least one time. It's when the month is not listed that I still get 1 as the return rather than 0.
Hi! Try one of these formulas:
=IF(ISERROR(FILTER('Child Care'!I5:I27,('Child Care'!I5:I27<>"")*('Child Care'!F5:F27="January"))), "", (COUNTA(UNIQUE(FILTER('Child Care'!I5:I27,('Child Care'!I5:I27<>"") * ('Child Care'!F5:F27="January"))))))
=IF(SUM(--('Child Care'!F5:F27="January"))=0, "", (COUNTA(UNIQUE(FILTER('Child Care'!I5:I27,('Child Care'!I5:I27<>"") * ('Child Care'!F5:F27="January"))))))
Wow!!! This is amazing and works perfectly....Counting the unique names in the column only if the given month is also selected and if both conditions are not met it returns a blank cell.
I replaced the "" with a 0 so the return will show on the spreadsheet as 0 if both conditions are not met and there are no issues at all.
Thank you again for your time in helping with this.
Hi,
I am using the count if formula to count unique URL domains for a list of website names. I want a partial match in the cell range B2:B1337 for one of the following below:
.gov.on.ca
.on.ca
.ca
.com
.ontario.ca
The Problem:
1. I have used Countif(B2:B1337,"*.ca*") to count the total number of websites having a .ca domain but ut ends up including .on.ca, .ontario.ca and .gov.on.ca for which I want separate counts. Could you suggest a way to eradicate the issue, how do I get an exact partial match in the call?
2. For the ontario.ca count, the count comes out wrong as a few website names have ontario as part of their name instead as the end of the URL
Hi! You can count second-level domains by counting the number of dots in the domain URL. Read more: How to count certain text/substring in a cell. For example, count "[domain].ca"
=SUMPRODUCT(((LEN(A1:A15) - LEN(SUBSTITUTE(A1:A15,".","")))=1) * (ISNUMBER(SEARCH(".ca",A1:A15))))
I hope this will help.
HI,
I have a list of names in a column _A and the following formulas in B2 - =UNIQUE(FILTER(A2:A475, A2:A475""))
and C2 =COUNTA(UNIQUE(FILTER(A2;A475,A2;A475"")))
Calculation options is on Automatic, but it is not running.
Is this correct? Perhaps I am not running it correct of the formulas are wrong?
Hi! Your formulae are incomplete. If you can describe in some detail what you are trying to do with these formulas, then I will try to help you.
I have a list of names in xls, aprox 475 rows with some blanks. Some of the rows have more then one name:
J.Davis
J.Otis
J.Kenner, K.Westerfield
J.Davis
J.Davis, D.Kilpela
J.Connelly
P.Roath, J.Berkus(Cahow)
J.Pickerel
The goal is to create a list of names and count of how many times they are on the list:
j.Davis 22
j. Otis 15
J.Kenner, K.Westerfield 1
J.Davis, D.Kilpela 15
J.Connelly 10
P.Roath, J.Berkus(Cahow) 2
J.Pickerel 7
Hi! If my understanding is correct, you can get a list of unique values in B2 using the formula:
=UNIQUE(FILTER(A1:A475, NOT(ISBLANK(A1:A475))))
To count the number of each of the unique values, try to enter the following formula in cell С2 and then copy it down along the column:
=COUNTIF($A$2:$A$475,B2)
The following tutorial should help: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique.
Hope this is what you need.
Super!! Thank you for both the formula and the tutorial!!!
HI, Just a quick follow up.
I should be able to adjust the column numbers in the initial formula :
=UNIQUE(FILTER(A1:A519, NOT(ISBLANK(A1:A519))))
It did not run. The one thing i did note was when I separated the names in the columns there was an extra space causing the counts to be incorrect. While adjusting that, numbers started to match up on the COUNTIF.
Based on the tutorial I created the following formula to confirm counts and it did not run:
=COUNTIF(A2:A475, "*Taylor*")
even with the space removed:
=COUNTIF(A2:A475,"* Taylor")
still no results.
slowly getting there, and appreciate your assistance!
example of error:
=UNIQUE(FILTER(A2:A2293, NOT(ISBLANK(A2:A2293))))
works @475
Happy dance!! figured it out !! thank you!!
Hi! I’m sorry but your description doesn’t give me a complete understanding of your task.
Formula COUNTIF(A2:A475, "*Taylor*") counts all cells that have the word "Taylor" in the text, including those with a space at the beginning. I can't understand what is not working for you.
Maybe this article will be helpful: Excel COUNTIF for blank and non-blank cells
If this does not help, explain the problem in detail.
Hi sir,
What is the formula to count unique words in a row excluding blank cells?
Thank you.
Hi! All the necessary information is in the article above. Read following paragraph: Count unique entries ignoring blanks. Since the UNIQUE, FILTER and COUNTA functions work with columns, convert columns to rows using the TRANSPOSE function. The formula might look like this:
=COUNTA(UNIQUE(FILTER(TRANSPOSE(A2:Q2),TRANSPOSE(A2:Q2)<>"")))
Hello,
I want to count how many times a value(text) is mentioned in Column A. I followed your guides (Listed below) but I wasn't able to figure it out.
Example:
Column A | Column B | Count
Name 1 | Unrelated | 2
Name 2 | Unrelated | 1
Name 1 | Unrelated | 2
When I use the formula =COUNTA(Distinct(A2:A815)) I get this Result
Column A | Column B | Count
Name 1 | Unrelated | 1
Name 2 | Unrelated | 1
Name 1 | Unrelated | 1
The formula copies itself downwards on its own column, which is what I want, but it edits the range by itself, like so
=COUNTA(Distinct(A3:A816))
=COUNTA(Distinct(A4:A817))
When I use =COUNTA(Unique(A2:A815)) it counts how many items are in the column except duplicates, while also editing its range while going down
Column A | Column B | Count
Name 1 | Unrelated | 612
Name 2 | Unrelated | 611
Name 1 | Unrelated | 611
Name 3 | Unrelated | 610
Name 4 | Unrelated | 609
How am I able to count each time a value is mentioned, and have the formula stay in a consistent range while copying itself on all rows?
I am on 365 using a single table for my entire work sheet (A2:A815). I created page breaks, not sure if its even relevant.
I believe you mentioned you don't work with 365? If that's correct, would you mind pointing me to the right person?
Guides I've followed:
https://www.ablebits.com/office-addins-blog/count-unique-values-excel/
https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/
https://www.ablebits.com/office-addins-blog/excel-unique-function-find-unique-values/
https://www.ablebits.com/office-addins-blog/excel-unique-distinct-values/
Hi! If I understand your task correctly, use absolute cell references to the data range. This article may be helpful: How to copy formula in Excel with or without changing references.
Thank you for posting this. It is simple, direct, and exactly what is necessary, no need to deal with pivots or vba or anything.