*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:

*range*))

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:

*range*))

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:

*range*,

*range*<>"")))

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:

*range*,

*criteria_range*=

*criteria*))), 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:

*range*, (

*criteria_range1*=

*criteria1*) * (

*criteria_range2*=

*criteria2*)))), 0)

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)

## 214 comments

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.

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)

Hi I would like to know how to count number of unique values that meets either of the criteria.

Taking the above example in “ Count unique values with criteria”, I want to count the total number of winners in basketball and volleyball.

May I know if there is a formula for such case? Thanks

Hi! To count the number of unique values by OR condition, use + (sum) instead of * (multiplication) in the formula. For example,

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) + (B2:B10=G1)))), 0)

Hi there,

I have 4 collumns

I have a table with values between rows 2 and 57, and between collumns C to F.

I want to count how many times in the whole table: Collumn C contains the text "On-Call" and the Collumn D OR Collumn E OR Collumn F text on the same row contains the text "Bank Holiday".

I can understand how I would do this if the intended logic was "and" where "or" is, but I cant figure out how to get it to work as I've written.

I've tried:

=COUNTIFS(C2:C57, "On-Call", OR(D2:D57="Bank Holiday",E2:E57="Bank Holiday",F2:F57="Bank Holiday"),"TRUE")

which didn't work.

Thanks!

Hi! The answer to your question can be found in this article: Count cells with multiple criteria (OR logic). For example,

=COUNTIFS(C2:C57, "On-Call", D2:D57,"Bank Holiday") + COUNTIFS(C2:C57, "On-Call", E2:E57, "Bank Holiday")

But wouldn't that count it twice? I'm after an "or" (not exclusive) not an "and" relationship between the contents of collumns D E and F. I want it to return a value of 1 if Collumn C contains "On-Call" and at least one (but potentially up to all 3) of collumns D E and F contain "Bank Holiday"

Hi! Try to use SUMPRODUCT function:

=SUMPRODUCT(--(C2:C20="On-call"),--(((E2:E20="Bank Holiday")+(F2:F20="Bank Holiday"))>0))

Hope this is what you need.

Hi,

Is there a way to use SPILL in a FILTER formula?

I've got a SPILL list of Unique locations.

=SORT(UNIQUE(TablePrint[Location]))

Secondly I want to show a dynamic list of Unique CaseID's for the locations.

=ROWS(UNIQUE(FILTER(TablePrint[Case ID];TablePrint[Location]=AO6)))

This works fine, but I have to fill the series manually to the same size as the first SPILL list.

I need to get that dynamically.

The Cell reference 'AO6' references to the first cell of the UNIQUE SPILL list of the first formula.

If I change this reference to 'AO6#' it give's a "#N/A".

I'm out of thoughts, so any help will be appreciated.

Ron

Hi! To extract the first value from an array of unique values, use the CHOOSEROWS function.

CHOOSEROWS(AO6,1)

I hope it’ll be helpful.

Hi, I have employee codes in column A, and their Gender in column B.

1234 Female

5678 Male

9101 Female

1213 Male

1234 Female

I want the count of females, excluding the duplicate values. For example, in the above table, the count of females should show 2 (Instead of 3) since 1234 is a duplicate value.

Hi! Pay attention to the following paragraph of the article above: Count unique values with criteria.

It covers your case completely.

Yes, it worked!

Please can you also help with the below

1234 Female United Kingdom

5678 Male Ireland

9101 Female United Kingdom

1213 Male Russia

1234 Female United Kingdom

Need the overall count of UK and Ireland employees excluding duplicates.

The formula I used: =IFERROR(ROWS(UNIQUE(FILTER(M643:M662,AF643:AF662="United Kingdom","Ireland"))),0)

It did not work. The above one is only pulling the count of United Kingdom. Please help

Hi! Using your data, the formula for calculating unique values by condition can be like this:

=IFERROR(ROWS(UNIQUE(FILTER(A1:C5,(C1:C5="United Kingdom")+(C1:C5="Ireland")))),0)

I recommend reading this guide: Excel FILTER function - dynamic filtering with formulas.

i need to distinct count with multiple criteria form raw data

Driver name City name 29-01-2023 30-01-2023 31-01-2023 01-02-2023

Anand K Bengaluru

Kumar K Bengaluru

Bheemana Gouda Bengaluru

Kakarla Sambamurthy Visakhapatnam

Hi! Your task is not completely clear to me. To calculate the number of values for several criteria, use this guide: How to use Excel COUNTIFS and COUNTIF with multiple criteria.

Hi, love this and have it working mostly, but i need some assistance with the way i have my data and formula.

For example if i have the following data, and i want to find how find how many unique models i have across Company 1 OR 2. The below should give a result of 3 unique models.

I would use =iferror(rows(unique(filter'A:A','B:B'=1))),0) to show me how many unique values for Company 1, but how do i get it to be either company 1 or 2.

col A Col B

Model Company

ABC 1

DEF 2

GHI 1

ABC 2

DEF 1

Hi!

If I understand correctly, just get the unique values in column A with the UNIQUE function. Сount these values using COUNTA function.

=COUNTA(UNIQUE(A:A))

Hello - I have a data tab where I have the following columns: Entity Name, Distribution Date, Distribution Amount

Is there a way to create a new column that would label each unique distribution in chronological orders?

Entity 1 and Entity 2 will both have a Distribution 1 label but might be on different dates

Thanks

Hi!

Unfortunately, I don't understand what you want to do. Give an example of the result.

Hi there,

I have been using this on a MacBook and I cannot get the command to work. I have a list of companies, which I cannot share due to confedeniality, but an overview is similar to this:

Company A - Acquisition 1 - USA

Company A - Acquisition 2 - USA

Company A - Acquisition 3 - France

Company B - Acquisition 1 - UK

Company B - Acquisition 2 - USA

Company B - Acquisition 3 - Japan

Company C - Acquisition 1 - USA

Company C - Acquisition 2 - China

Company C - Acquisition 3 - France

Company C - Acquisition 1 - Germany

Company C - Acquisition 2 - China

Company C - Acquisition 3 - China

So I want to count the number of countries that are acquired per Company but when I run the IFERROR(ROWS(UNIQUE(FILTER...) command every one comes out as zero. I ran it before and it worked fine but I cannot figure out if I am doing something wrong.

Thanks!

Dee

Hi!

I can't give advice on a formula that I don't see. Think about what has changed in your data.

Hi!

My apologies - I am relatively new to these forums and to data analyses within excel, so I am sorry for not giving the formula!

If the company names are in column A, and the countries in column C, then I am using =IFERROR(ROWS(UNIQUE(filter($C$2:$C$17220,$A$2:$A$17220=A2))),0)

I get the value of 0 for every row and cannot understand what I am doing wrong! Apologies for my ignorance if it is something obvious. I very very much appreciate any help you have to offer!

Thanks,

Dee

Hi!

Your formula works for me. There may be a problem with the version of Excel. Try the formula without IFERROR and you will see where the error is.

=ROWS(UNIQUE(FILTER($C$2:$C$17220,$A$2:$A$17220=A2)))

Hi Alex,

I have query, I have master data with different items.

I would like to have a unique count of multiple condition. That is unique count should come depending on the other columns condition. That is countifs(a2:a40,"UTCL", d2:d50,""&"" and so on..

Kindly help this without pivot table and array.

Regards

Iyer

Please clarify your specific problem or provide additional information to understand what you need.

Hello,

Client Engagement Team Lead Stage

Akanksha Hot

Akanksha Cold

Akanksha Cold

Akanksha Cold

Akanksha Hot

Akanksha Hot

Akanksha Hot

Akanksha Hot

Akanksha Warm

Akanksha Warm

Akanksha Warm

Akanksha Warm

Neha Hot

Neha Hot

Neha Hot

Neha Hot

Formula, as below requirement. Eg

Akanksha

Hot - 9

Cold - 3

Warm - 4

Hello!

You can find the examples and detailed instructions here: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique.

Hi Ablebits Team, thank you for this post, this formula have saved me a lot of time and kudos for giving me new knowledge for my work.

I do have a question, in using the same data in your article, example on taking how many win for Basketball and under age 18 that use the below formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<F2)))), 0)

how do I then extend this formula to see how many win for Basketball and Hockey under age 18 for example? I try part of the formula (B2:B10=AND("Basketball","Hockey")) in excel it doesn't work. Could you advise the best formula I can use for this issue?

Right now on my work I have similar issue on my data and I can't be able to find the best formula to count multiple criteria under the same column as above. Thank you in advance for your help!

Hi!

If you write the second criterion in cell G1, then add the second condition to the formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, ((B2:B10=F1)+(B2:B10=G1)) * (C2:C10 < F2)))), 0)

See examples here: Filter based on AND as well OR criteria.

Hi,

I have a data of meetings which is datewise which is bifurcated week wise and month wise and then in house, video call and outdoor meetings. I want to pull the summary of this data like how many meetings are done per week unique, inhouse and outdoor.

I could extract the summary using countifs but cant find the unique fields. can you please help

Hi!

I don't see your data and I don't understand why you can't use the COUNTIFS function. Look for the example formulas here: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.

Also you can use SUMPRODUCT function to conditionally count cells with multiple criteria.

If this is not what you wanted, please describe the problem in more detail.

Date Week Month Action type Firm Name

10-Aug-22 2 August Indoor ABC

17-Aug-22 3 August Indoor DEF

26-Aug-22 4 August Indoor ABC

23-Aug-22 4 August Outdoor EFG

30-Aug-22 5 August Outdoor EFG

30-Aug-22 5 August Call XXX

1-Sep-22 1 September Outdoor YYY

1-Sep-22 1 September Outdoor KKK

15-Sep-22 3 September Call BBB

15-Sep-22 3 September Outdoor AAA

17-Sep-22 3 September Call BBB

17-Sep-22 3 September Indoor YYY

18-Sep-22 3 September Call YYY

20-Sep-22 4 September Outdoor XXX

20-Sep-22 4 September Outdoor ABC

This kindof data i have

for count i am using below mentioned formula but i want to count unique filed

{=COUNTIFS($D:$D,"September",$C:$C,"3",$E:$E,"Call")}

Please suggest what criteria to add

Hello!

I don't quite understand what unique records you want to count. I think these examples will help you: Count unique values with criteria. I hope it’ll be helpful.

I want to count unique firm name for the week for outdoor meeting or call or inhouse meeting. because in data we have met 2 or three firms multiple times but i want to count it as one. so unique count i need

Hi!

Then use the link to the article I gave you.

I am using MSO 2016 and lower version please suggest me formula for that.

Hi thanks for this.. can you please help me with some modification in the formula. Is there a way to exclude a certain name in the unique range. Like with the use if your example lets say i want to exclude david in the count.

Hello!

The answer to your question can be found in this article: How to get a list of unique values based on criteria.

Hello,

Can someone assist with how I would show the count of unique firms per manager?

O2:O100 has Firm #

Q2:Q100 has Manager Name

I can find the count of the overall unique firms, but am having trouble inputting a formula just to show that specific manager's count.

Thank you.

Hello!

Please re-check the article above since it covers your task.

Try to use the recommendations described in this paragraph: Count unique values with criteria.

Hello.. Thank u for you sharing,

There is another way to count unique with multiple criterias, if the criterias more than 20?. It takes to long syntax if i put one by one critera like you shared..

Thank you so much..

Hi Good day,

Column A Column B Column C

A AA XX

A AA XX

A AA XX

A AB YY

A AB XX

B BB XX

B BB XX

B BC YY

B BC XX

B BC ZZ

From the sample data above, I want to count how many distinct text in column C that group by column A)and column B. The result I wish to get is like this:

Column A Column B Column C

A AA XX 1

A AA XX 1

A AA XX 1

A AB YY 2

A AB XX 2

B BB XX 1

B BB XX 1

B BC YY 3

B BC XX 3

B BC ZZ 3

For group A, AA only XX in the group show 1 for each row, For group B,BC there are YY, XX and ZZ in it so show 3 for each of them.

I've tried using this formula =

SUM(--(FREQUENCY(IF($A$2:A2=A2,MATCH($C$2:C2,$C$2:$C2,0)),ROW($C$2:C2)-ROW(D2)+1)>0))

but I didn't get what I want T.T

Hope you understand my question. Thanks in advance for helping.

Hello!

Try to use the recommendations described in this article: Count unique values with multiple criteria.

If I understand your task correctly, the following formula should work for you:

=IFERROR(ROWS(UNIQUE(FILTER($C$1:$C$10, ($A$1:$A$10=A1) * ($B$1:$B$10=B1)))), 0)

You can copy this formula down along the column.

Thank you so much for reply.

Sorry, forgot to tell. I'm using Microsoft Excel 2010 and it don't have function UNIQUE and FILTER. Is there any functions suitable for Microsoft Excel 2010 in this situation?

Thanks again.

Hi!

Unfortunately, I do not know of a way to solve this problem with your Excel version.

Hello,

I want to count what is the number of rooms based on the date entered and cell colour. If there is no date entered, I don't want it to be counted. The rooms is arranged in multiple rows and column.

As example, the total number of blue cells is 458. In that 458, 110 cells have date meanwhile the rest is empty. So, how do I write formula for this as I am dealing with multiple colours as well.

Hello!

To count the number of cells with a specific color, you can use the user-defined function CountCellsByColor. You can find the examples and detailed instructions here: How to count and sum cells by color in Excel.

You can define empty cells with the ISBLANK function.

I hope this will help, otherwise don't hesitate to ask.

Hi, I'm trying to count unique values with multiple criteria but nothing seems to fit what I need.

I have a sheet with data and a sheet with results within the same book.

In a cell on the results sheet I am trying to count the number of unique items (job number) on the data sheet that match two sets of criteria, because the same criteria could have multiple lines under the same job number (for different months).

Eg on the data sheet:

Job# Month Customer Status

123 Jan Joe's jugs Closed

123 Feb Joe's jugs Closed

124 Jan Joe's lamps Open

125 Feb Jim's tables Closed

126 Feb Joe's pots Closed

I'm looking for the number of jobs on any account that belongs to Joe (so using a wildcard that Sumproduct can't handle) that are closed, but with unique job numbers (due to the spillover of job into other months). ie I'm trying to get a formula to give me a result of 2 [job 123 and 126], but can't find anything that works. Closest I've come is COUNTIFS, but I can't remove the duplicate job numbers so it will give me 3 [job 123 twice plus 126]. If I try to incorporate UNIQUE into that I get a SPILL error.

Hi!

Please re-check the article above since it covers your task.

Here is the formula for your task:

=COUNT(UNIQUE(FILTER(A2:A6,(C2:C6="Joe’s")*(E2:E6="Closed"))))

Hello -

I'm trying to count the numbers of new projects in a certain type of work. Within a project might be multiple jobs, so i'm trying to count the unique project number by job type.

I'm using the IFERROR(ROWS(UNIQUE(range, criteria_range=criteria))), 0) formula, but it is not pulling the expected results. It also changes the results if the table is filtered differently.

Is there a way to send you my excel sheet for more info?

=IFERROR(ROWS(UNIQUE(FILTER("Range of Project #'s","Range of job Types"="Job Type look for count of"))),0)

Thank you!

Chad

Hi!

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.

Hi, As per shared expample for Count unique values with criteria, I want to find the count of multiple sport for winners. Means if Andrew having intrest in 2 sports (like basketball- 2 and Hockey-1). I want answer number 2.

Hi!

Use another formula for counting unique values:

=IFERROR(ROWS(UNIQUE(FILTER(B2:B10, (A2:A10=E5) ))), 0)

E5="Andrew"

in sheet1 at A:A i have data as medicine names, At sheet 2 from C:C i want to create autocomplete drop down list from the data , which should complete by just 2-3 letters, based on data at sheet1

please help

Hello!

Data Validation doesn't have an AutoComplete feature. You need to use Combo Box. To insert it, use the menu Developer > Insert > Combo Box (ActiveX Control).

Hello,

I am trying to determine the amount of "New Customers" we received in a given FY. This data has opportunities that are split between reps, giving me an extra "New Customer" since it has a different opportunity ID. I am trying to use countif in a column by "New Customer" while using Sumproduct on another column (account name) to not count any "New Customers" that have the same account name.

Hi!

Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

I need help please. I want to count the active months of production from a specific date, including the zeros.... how do I do this if all the dates are different?

Hello!

You can get a list of desired months and years using the formula

=IF(A2>DATE(2021,1,1),MONTH(A2)&YEAR(A2),"")

Then count the number of unique values in this list using this guide: Count unique and distinct values in Excel.