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
thank you for the answer
it answered and it didn't at the same time, topic you gave nd here is example:
=IFERROR(ROWS(UNIQUE(FILTER(D2:D6, (A2:A6=J5) * (B2:B6=K5) * (C2:C6=L5) * (F2:F6=M5)))), 0)
"J5 is 1, K5 is A, L5 is 1 and M5 is done"
here I have multiple arrays, if (zone1->area1->type1is done) it will return (3)
I want to figure out how it will return "1"?
as house no. 2 & 3 are not done completely.
house no.2 only one product is done with production & delivery the other is not.
---A-------B------C-----------D--------------E-------------F---------------G
zone l area l type l house no. l product l production l delivery
--1------- A ------1-----------1------------ z1-------- done--------- done
--1--------A ------1---------- 1------------ z2-------- done ---------done
--1------- A ------1---------- 2------------ z1-------- done--------- done
--1------- A ------1---------- 2------------ z2------------X--------------X
--1------- A ------1---------- 3------------ z1-------- done--------- done
--1------- A ------1---------- 3------------ z2-------- done -----------X
Hello!
The line "–1 ——- A ——1 ———- 2 ———— z1 ——– done ——— done” means that house 2 is done.
It is necessary to change the list of criteria and add additional data to the table.
Hello Alexander,
hope you are well, I need help to make a formula to count unique value with multiple criteria, for example:
count how many houses in (criteria1)zone 1,(C2)area A, (C3) type 1, done production,
hence: if production is "done" for (1/2/3) products in a house, then it count as 1.
when I use sum frequency with multiple criteria it gives me error "too few conditions".
A B C D E F G
zone l area l type l house no. l product l production l delivery
1 A 1 1 z1 done done
1 A 1 1 z2 done done
1 A 1 2 z1 done done
1 A 1 2 z2 done done
1 A 1 3 z1 done done
1 A 1 3 z2 done done
1 A 2 1 z1 done done
1 A 2 1 z2 done done
1 A 2 1 z3 done done
note: zones(1,2,3,4) are not duplicates
but for each zone -> area(1,2,3) type(A,B,C..), house(1,2,3...), products(z1,z2) have duplicates.
Hello!
You can find the examples and detailed instructions here: Count unique values with multiple criteria.
I hope I answered your question. If something is still unclear, please feel free to ask.
The =UNIQUE formula does not exist. It only gave me #NAME?
How did you get that? or is there any other formula same results it show.
Hello, how do you get a distinct list across multiple workbooks or worksheets?
Also, how do you combine VLOOKUP and index match.
let say I have existing list and I want to add list from another workbooks but that workbook is continuously adding up a list. been trying different formulas but it's all not working or something is missing on the formula.
Hello!
Please have a look at this article — VLOOKUP across multiple sheets in Excel with examples.
Hello!
I recommend reading this guide: How to get a list of unique and distinct values in Excel.
UNIQUE function is only available in Excel 365
Hello Ablebits Team,
I have data (text) in a column say column A with another data (also text) in column B assigned to the each data in column A. I want a formula that can count the unique occurrences of the data in column A using the data in column B as reference.
Data
Column A Column B
Leo A
Pete C
Bright B
June A
Mike C
King D
Diana D
Alice C
Bright B
Leo A
Tom B
Results
A = 2
B = 2
C = 3
D = 2
Hello!
You can find the examples and detailed instructions here: Count unique values with criteria.
This should solve your task.
Column A Column B Column C (Result)
John 12345 1
John 12345 0
John 12345 0
Peter 67890 1
Peter 67890 0
Peter 67890 0
I want only the first entry to be counted as 1 in column C and the rest duplicate entries as 0.
I want result in single Column C without introducing new column.
I tried doing concatenate but not working can u please help.
=IF(COUNTIF($A$2:A2,A2)=1,1,IF(COUNTIF($D$2:D2,D2)=1,1,"-")) - Working but the Name if repeats with different number it not counting as 1
=IF(AND(COUNTIF($A$2:A2,A2),COUNTIF($D$2:D2,D2)=1),1,"-") - Working but the Name if repeats with different number it not counting as 1
Column A Column B Column C (Result)
John 12345 1
John 12345 0
John 12345 0
Peter 67890 1
Peter 67890 0
Peter 67890 0
John 005002 1
John 12345 0
Peter 45678 1
Peter 67890 0
Hi Alex,
In a column A i have many duplicate or repeated entries i want only the first entry to be counted as 1 in column B and the rest duplicate entries as 0. can u please help me.
Example
Column A Column B
12345 1
12345 0
23456 1
23456 0
12345 0
23456 0
Hello!
Please use the following formula
=IF(COUNTIF($A$1:A1,A1)=1,1,0)
You can learn more about COUNTIF function in this article.
Hi Alex,
Thank you so much Alex,
there is 1 more problem Can u please also help me for the below
Column A Column B Column C (Result)
John 12345
John 12345
John 12345
Peter 67890
Peter 67890
Peter 67890
I want only the first entry to be counted as 1 in column B and the rest duplicate entries as 0.
I want result in single Column C without introducing new column.
I tried doing concatenate but not working can u please help.
Hi!
Add another condition and use the COUNTIFS function:
=IF(COUNTIFS($A$1:A1,A1,$B$1:B1,B1)=1,1,0)
Thank you so much
Hi Alex,
I have successfully applied the UNIQUE function, but I want to see how many times all unique (distinct) inputs were present in the original column. For example:
A
1
1
2
The UNIQUE formula would give me: 1 and 2. In addition I want to see how many times the 1 and the 2 values were present in column A. Is this possible?
Thanks, Daniel
Hello!
Here is the article that may be helpful to you: Excel COUNTIF examples.
Hi,
I am looking for a method of how to return a list of cells and count with a partial match. I have used the formula below for returns with cells for a complete match.
=IFERROR(INDEX($A$2:$A$115, MATCH(0, IF($C$2:$C$115="monitor", COUNTIF($H$6:$H6, $A$2:$A$115), ""), 0)),"")
I have used the COUNTIFS function to list the count for the partial matches but it is not replicating the same outcome with partial matches - is this possible?
Thanks, Tom
Hello!
To count the number of partial matches of the word "monitor" in a range, use a formula like this:
=SUM(--(ISNUMBER(SEARCH("monitor",B1:B25,1))))
I hope this will help.
Hi Alex,
Apologies I forgot to mention that the list would be pulling the data from adjacent cells and then populating the lists, i.e. column A has variables and column B has comments.
- The formula I used in the opening statement pulls from a complete match
- The formula I need would be with a partial match
I have sent an email to support address with the workbook if it makes more sense!
Thanks, Tom
I am trying to count unique value with criteria. The criteria are to recognize the word starting with letter A in the second column. I changed the function FIND by SEARCH to use the wildcat but it is not working, maybe you can advise. I
IFERROR(ROWS(UNIQUE(FILTER(G2:G2367,ISNUMBER(SEARCH("*A*",H2:H2367,1))))), 0)
Hello!
Use a formula like this:
=ROWS(UNIQUE(FILTER(A2:A10, IFERROR(SEARCH("A",B2:B10,1)=1,FALSE))))
I hope it’ll be helpful.
I have many columns where each one has different dates
For example Column A
Box A1 is 02/24/2021
Box A2 is 02/25/2021
Box A3 is 02/26/2021
Box A2 is 02/24/2021
I should Know the date 24 how many times is found in this Column and put it in a Box and Multiply it By 5$
So: IF Column A has let's say 3 times (02/24/2021) that means 3 x 5$ should be equal to 15$
How Can I do this???
Hi,
To count how many times a value appears in column A, use the COUNTIF function.
=COUNTIF(A1:A100,D1)
where D1 -- 02/24/2021
I hope I answered your question. If something is still unclear, please feel free to ask.
Hello there I need a favor.
I need someone to help me to put a formula in a excel
I use the 2016 Version
can anyone help me?
Hi,
For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Hi there, thank you for this very helpful tutorial! I've created the below formula based on your tips:
=IFERROR(ROWS(UNIQUE(FILTER('All Session Data'!$G:$G,('All Session Data'!B:B=EN1)*('All Session Data'!C:C=EN2)*('All Session Data'!I:I=EN4)*('All Session Data'!R:R=EN9)*('All Session Data'!T:T=EN8)))),0)
The formula is supposed to count all unique values in column G based on the 5 criteria I've listed. I'm getting a 0 for the result. I'm working with 24,500 rows in Google Sheets. Any suggestions for a solution?
Hi
I have three collums with similar values, the goal is to find the values occuring the most.
I want to find the unique values in the two/three collumns and use a countif statement. However, whenever i use unique on more than one collumn its registered as unique rows rather than analysing the individual cells - how is this mitigated?
Best Regards
Jens
Hello!
Your first phrase contradicts the second phrase. Do you want to find the most common values or unique values?
Based on your description, it is hard to completely understand your task. I am assuming you want to extract unique values from cells of multiple columns.
However, I’ll try to guess and offer you the following array formula:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"") * (COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
Assuming your values are in the range A2:C9, enter the formula in cell E2.
In the above formula, A2:C9 indicates the range of cells from which you want to extract unique values.
E1 is the first cell in the column where you want to place the result.
$2:$9 is the rows containing the cells you want to use.
$A:$C indicates that these columns contain the cells you want to use. Please change them to your own.
Press Shift + Ctrl + Enter and then drag the fill handle to extract unique values until blank cells appear.
Hi,
I have data, let's say A to F, A to E is the criteria and i need to count distinct or unique from column F with criteria from A to E, but all i got is not how much distinct counts, it's only count how much times the duplicate is. Can you help me ?
Hello!
Please check out this article to learn how to count unique values with multiple criteria.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Hi, thank you for your feedback.
I already tried that way but i think i have different data so i'm a bit confused where to put. Refer to your link, data has repetition, while i already deleted duplicate ones so it's unique. Can i still use that formulas and put it in C2 ?
Hi,
Unfortunately, without seeing your data it is impossible to give you advice.
Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
I have data which records in the Cell the year the equipment is being replaced. I have in another cell the cost of all equipment purchased. I would like excel to give me the total cost of equipment replacement in each of the following 5 years. Dates have been setup to have a total after excel searches the year and adds the amount. Could you assist please.
Hello!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.
I have a formula I use but this one does not count distinct numbers
=IF((COUNTIFS(Data!$J:$J,A8,Data!$I:$I,'Staff Enc'!$B$4))=0,"",(COUNTIFS(Data!$J:$J,A8,Data!$I:$I,'Staff Enc'!$B$4)))
Does anyone have a suggestion on how to do something similar but count distinct numbers?
I can send a sample report of how the formula works for me. Don't see were I could post it though.
Hi,
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Thanks, your "Count unique values with multiple criteria" section worked a treat!
Hi all,
Trying to find a formula for a stock portfolio and it seems i struggle a lot.
I have a DB of purchases and sales in one tab (column with portoflio names - 5 and column with the ticker) and a summary page where i have all stocks and their performances with eventual rebalancing actions to be taken.
What I want to make sure is that the number of stocks reported in the summary page are the unique number of stocks i have in the db tab (aim is to get a warn if i didn't include a new bought or sold stock in the summary page as rebalancing may be wrong
Condition 1: Unique stock counts (number of - in the DB i have more purchases of the same stock)
Condition 2: Count of Unique stock number among 5 different portfolios.
I hope i clarified it and thanks in advance!
count unique values with the below data
Sales Person Items Account
A X XYZ
A X XYZ
A Y ABC
B X PQR
C Z MNL
C Y HGF
D X MTR
D Y GTR
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question. If you describe your problem in detail, I will try to help you.
Hello,
Im trying to count all cells with unique values within a column range (K2:K101) excluding any cells with value 0.
I believe the formula should look something like =COUNTA(UNIQUE(FILTER(K2:K101, K2:K101"0"))) however this consistently gives me incorrect results.
Do you have suggestions?
Hello!
If I got you right, the formula below will help you with your task:
=COUNTA(UNIQUE(FILTER(K2:K101, K2:K101<>0)))
I hope my advice will help you solve your task.