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)
224 comments
hi,
i want to count the unique number of invoice per salesman, what formula should use?
Hi! You can find the answer to your question in this article: How to count unique values in Excel: with criteria, ignoring blanks.
Hi,
I'm trying to count the number of trainings individually each calendar month.
The outcome I would like is if there are 5 trainings in a month, but 2 trainings were the same topic delivered on different days my total number of trainings would equal 5 and the number of sessions would be 7.
If there were no trainings delivered in a month, the total would show 0, rather than 1 which is what I get now using this =(COUNTA(UNIQUE(FILTER(B2:B11"")))
The session count works with the basic formula =COUNTA(B2:B11)
I have tried replacing the "" with 0, but if there are no trainings listed I get a result of 1.
I appreciate your help.
Hello Brenda!
Pay attention to the following paragraph of the article above: Count unique entries ignoring blank cells. Try this formula:
=COUNTA(UNIQUE(FILTER(B2:B11, B2:B11<>"")))
If the training dates are written in column C, the formula for solving your problem might look like this:
=COUNTA(UNIQUE(FILTER(B2:B11&C2:C11, B2:B11<>"")))
For the detailed instructions, please see: Excel FILTER function - dynamic filtering with formulas.
I still get a result of 1 using the =COUNTA as listed, however, I did find this worked for me so passing along for others.
=IFERROR(ROWS(UNIQUE(FILTER(B2:B11,B2:B11""))),0)
I've updated the formula =COUNTA(UNIQUE(FILTER(B2:B11, B2:B11""))) and I still get a result of 1 when there is no data in a given month.
If we didn't provide any trainings in January or February those two months the # of Trainings delivered shows as 1 rather than 0.
Is this because of the COUNTA function being used? Is there something else I can try so I get a result of 0 in this example?
I could also manually put in a 0 once the month has passed and I know there were no trainings delivered. :)
Hi! The formula I sent to you was created based on the description you provided in your first request. The formula you wrote in the first question could not count values for a specific month. The values you calculated contained dates for only one month. If you want to filter the dates for specific month and count unique values, try this SUMPRODUCT formula:
=SUMPRODUCT(IF(ISERROR(UNIQUE(FILTER(B2:B11,(MONTH(B2:B11)=D1)*(B2:B11<>"")) & FILTER(C2:C11,(MONTH(B2:B11)=D1)*(B2:B11<>"")))),0,1))
MONTH function will return the month number for each date. You compare this number with the month number written in cell D1. To filter the data for that specific month, excluding blank cells, use the FILTER function. IF ISERROR function returns 0 if there are no values for the specified month. For values that meet the conditions, 1 is returned. The SUMPRODUCT function is summarizing the values.
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?
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.
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"))))))
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<>""))))
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!
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.
Happy dance!! figured it out !! thank you!!
example of error:
=UNIQUE(FILTER(A2:A2293, NOT(ISBLANK(A2:A2293))))
works @475
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.
Muchas Gracias!!!!!!!!!!
Hi Alex,
I have below data, and want formula to get the result listed down.
Column A Column B
TA115 Matt
TA115 Rani
TA115 Rani
TA678. Swetha
TA678 Swetha
I need Unique entry of Application numbers which are in Row A & respective staff assigned to -
Example of the result -
Column C Column D
TA115 1
TA678. 1
Column C Column D Column E
TA115 1 Matt
TA678 1 Swetha
Hi! Sorry, it's not quite clear what you are trying to achieve. The example of results don't match your questions. Maybe this article will be helpful: Get a list of unique values in Excel.
Hi,
I am looking to count the number of unique species by their phyla in 3 different locations. I have a sheet with three columns: site, species, and phyla. The sites are A, B, and C. Species can be grouped into phyla. I am looking for a formula that can produce the number of unique species in each phyla category per site so that I can compare species richness across sites and make graphs that are color coded by phyla.
Trying the method described in the "Count unique values with multiple criteria" section produces an error. Seems like the error is with FILTER as that doesn't work by itself.
Here's an example of the sheet for visualization, but it is many thousands of rows in actuality. :)
site species phyla
A dalli arthropoda
A dalli arthropoda
A chondra rhodophyta
A caula rhodophyta
B dalli arthropoda
B glandula arthropoda
B egregia ochrophyta
B chondra rhodophyta
C dalli arthropoda
C egregia ochrophyta
C silvetia ochrophyta
C caula rhodophyta
Hi! I don't know what is not working for you, as you haven't written your formula. Use the recommendations from the article above and try this formula for an example:
=COUNTA(UNIQUE(FILTER(B2:B13,(C2:C13="arthropoda")*(A2:A13="A"))))
HI
I want to count unique value with condition, for example: i have 2 coulmns i want to filter 1401 and count unique value in coulumn requisit
Please help me.
TNX
Requisit year
656100 1401
656100 1401
692935 1402
338886 1398
334001 1398
307551 1398
307556 1398
313528 1398
325855 1398
304155 1398
325855 1398
648516 1401
648516 1401
648516 1401
648516 1401
Hi! Pay attention to the following paragraph of the article above – Count unique values with criteria.
It covers your case completely.
HI,
I have a Calculator worksheet that pulls the following from another worksheet in the workbook:
=COUNTIF('test page Cost Centers'!F:F,TRUE).
This is used to divide an invoice amt between certain providers by suite # (F:F)
Here are the column headings for the Calculator worksheet.
A B C D E F G J
Subtotl Tax Suite Specialty SubAcct #CostCtr ProviderCode Provider Name
The 'test page Cost Centers" worksheet has the following formula:
=C3=Calculator!$C$6
Here are the column headings for the Cost Center worksheet.
A B C D E F G
cost ctr Provider Suite Specialty (null) Match C Match D
How do I change the COUNTIF formula to count either the suite#(F:F) and/or the Specialty(D:D) when muliple specialty are in the same suite#?
I get a little confused with the unique values statement but think this is something needed to pull the numbers I need.
Thank you for your time.
Hi! To do counting on multiple conditions, use the COUNTIFS function. Here is the article that may be helpful to you: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria. I hope it’ll be helpful.
Hi Sir, I have 3 columns of below:
Column A Column B Column C
20/7/2023 2023-07 Pizza Hut
21/7/2023 2023-07 KFC
22/8/2023 2023-08 Pizza Hut
23/8/2023 2023-08 KFC
24/8/2023 2023-08 Pizza Hut
25/8/2023 2023-08 KFC
I have used below formula to get the figure of count of KFC in 2023-08, but only get answer = 1
=IFERROR(ROWS(UNIQUE(FILTER($C:$C,($B:$B="2023-08")*($C:$C="KFC")))),0)
How to slot in the criteria with date (Column A) inside the formula to get answer =2 ?
Hi! Use column A to count unique values. Modify the formula:
=IFERROR(ROWS(UNIQUE(FILTER($A:$A,($B:$B="2023-08")*($C:$C="KFC")))),0)