Comments on: How to count unique values in Excel an easy way

A couple of years ago, we discussed various ways to count unique and distinct values in Excel by using traditional array formulas. Today, we will show you a much simpler solution with the recently introduced dynamic array functions. Continue reading →

Comments page 2. Total comments: 224

  1. 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

    1. 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)

  2. 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!

      1. 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"

  3. 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

  4. 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.

    1. Hi! Pay attention to the following paragraph of the article above: Count unique values with criteria.
      It covers your case completely.

      1. 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

  5. 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

  6. 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

  7. 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

  8. 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

      1. 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

        1. 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)))

  9. 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

  10. 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

  11. 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!

  12. 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

      1. 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

          1. 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

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

  14. 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.

  15. 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.

    1. 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.

  16. 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..

  17. 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.

    1. 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.

      1. 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.

  18. 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.

  19. 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.

    1. 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"))))

  20. 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

    1. 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.

  21. 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.

    1. Hi!
      Use another formula for counting unique values:

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

      E5="Andrew"

  22. 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

    1. 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).

  23. 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.

    1. 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.

  24. 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?

  25. Hello All,

    How do I find the total sum of repeated count in a column? E.g If I have A1, A2, A3, A3, A2, B2, B2. I want the sum of repeated count to be 6

  26. Hi, I need help please!
    Im trying to calculate how many unique delivery numbers I have that are
    A. under 30 days overdue
    B. inbetween 30 and 90 days overdue
    C. over 90 days over due.
    I've just tried =IFERROR(ROWS(UNIQUE(FILTER(E:E,C:C<31))),0) but it's counting ones that are over 90 days overdue too. is there something else I need to do to stop this?

  27. Hi there,

    Thank you for the article - really helpful. I'm looking for a way to list the unique values across an entire table or array. For example my table values look like:

    Apple Pear Orange
    Banana Lemon Apple
    Orange Banana Pineapple
    Berry Lemon Apple

    I want to generate the list of unique values only across the table:

    Apple
    Pear
    Orange
    Banana
    Lemon
    Pineapple
    Berry

    Any advice on the best way to extract this would be much appreciated!

  28. Hi. How can I count unique values under a column? I'm trying to see how much customer feedbacks there are. Let's say I want to know how many people said excellent or professional.

    Column F
    1. very professional and the service provided was excellent
    2. customer service was great
    3. professional
    4. my problem got resolved
    5. the woman I spoke with was excellent and professional

      1. Thank you for your help!

  29. Hi there

    I have a dataset with an ID number in column A and a response of yes or no to 2 questions in columns B & C. I am trying to get an accurate chart of the results, where i take a unique count of ID Number for each of the 3 outcomes to the 2 questions, YES & YES = Outcome 1, YES & NO = Outcome 2 and NO & YES is outcome 3.
    My problem is that ID number 1 below has returned a different response to Q1 in the first row than it did in row 2. So my results return a value for ID number 1 in outcome 1 and also a value for outcome 3. Where an ID number answers YES in Q1 it should always trump any additional response of NO. So the unique count for Outcome 3 should not include ID Number 1.
    I can do it manually and find the duplicates and remove the incorrect records, but this is a long process as the dataset is quite large. I need to automate this process in some way. Any help is most appreciated.

    ID Number Q1 Q2
    1 YES YES
    1 NO YES
    2 YES YES
    2 YES YES
    3 NO YES

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"Yes")>0,"Yes","No") & IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"Yes")>0,"Yes","No")="YesYes",1, IF(IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"Yes")>0,"Yes","No") & IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"Yes")>0,"Yes","No")="YesNo",2,3))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  30. Hii Sir

    can u please clear it

    if use one row bt different words and finally i want particular word count so what formula i can use?

  31. I have a spreadsheet of census information which contains the columns "Surname", "Given Name", and "Occupation". Frequently the data in each of these columns is the same (people with the same occupation, people who have the same surname, or given name). I want to create a formula which will identify each unique value in the column, and then count the number of instances of that unique item. So for example, I want to count the number of people who have a specific occupation. Or the number of people who have a specific surname, etc. I am using this formula to find and sort the unique values in the column, and it is working fine:
    =SORT(UNIQUE(J2:J3135))
    What I'd like to add to it is " (nn)" at the end of the formula shown so that the output (for the occupation field) would look like this (where these are the unique values identified by the above formula in the J2:J3135 range):
    Apprentice (14)
    Artist (1)
    Blacksmith (4)
    Bookkeeper (2)
    Clergyman (3)
    ...etc.
    So far I am not having much luck adding the counting part of this.
    Many thanks in advance.

      1. Hello Alexander,

        I am working on a spreadsheet that is live, and ever changing. The question asked above is the most similar to what I am attempting to do. The two column titles are "Job Number" and "Company". Here is an example:

        1 Company A
        2 Company A
        3 Company B
        4 Company C
        5 Company D
        6 Company D
        7 Company E

        The UNIQUE function I am using is working correctly. Each Rep generally has 50 assigned "Job Numbers". So, In Cell C1, I enter the unique formula, and it outputs correctly:

        Company A
        Company B
        Company C
        Company D
        Company E

        What I would now like to do, is link the output in Column C, with a Total in column D. With the end result being:

        Company A | 2
        Company B | 1
        Company C | 1
        Company D | 2
        Company E | 1

        From the article that you linked above, I see how you do this. However, as jobs complete, that column is removed. This is why I would like to tie Column C and Column D. If Company B is completed and removed, This is what the data then looks like:

        Company A | 2
        Company C | REF!
        Company D | 1
        Company E | 2
        | 1

        I really hope this makes sense haha!

        2. However, as jobs complete, that ROW*** is removed. I apologize.

  32. =COUNTIFS($J:$J,"name",$N:$N,"name") . one more column need to take and need distinct count from that . please help

  33. Dear Ablebits,

    I am trying to find the number of unique agents who sold in 1 week and didn't sell in the next week. Like in the table below, Agent 3 and 4 sold in week 1 and didn't sell in week 2.

    Date Week Agent Name Sales
    1/1/21 1 Agent 1 1
    1/1/21 1 Agent 2 1
    1/1/21 1 Agent 3 1
    2/1/21 1 Agent 1 1
    2/1/21 1 Agent 2 1
    2/1/21 1 Agent 3 1
    2/1/21 1 Agent 4 1
    3/1/21 1 Agent 1 1
    3/1/21 1 Agent 2 1
    3/1/21 1 Agent 3 1
    9/1/21 2 Agent 1 1
    9/1/21 2 Agent 2 1
    9/1/21 2 Agent 5 1
    10/1/21 2 Agent 1 1
    10/1/21 2 Agent 2 1
    10/1/21 2 Agent 5 1

    Using the unique count formula, I have tried using the formula below but I have been getting the calc error -
    FILTER(Daily_sales_data!O2:O10839,(Daily_sales_data!K2:K10839=E2-1)*(Daily_sales_data!K2:K10839=(E2-2)))
    where;
    Daily_sales_data!O2:O10839 - similar to Agent Name column above
    Daily_sales_data!K2:K10839 - similar to Week column above
    E2 = Week 3, so E2-1= Week 2 and E2-2= Week 1

    Can you help me know what I am doing wrong?

    1. Hello!
      Your data is written in A2:D17 and the week number is written in E1. To calculate how many agents sold in week 1 and not sold in week 2, try the formula

      =SUM((COUNTIFS(C2:C17,UNIQUE(C2:C17),B2:B17,$E$1)>0) * (COUNTIFS(C2:C17,UNIQUE(C2:C17),B2:B17,$E$1+1)=0))

      Hope this is what you need.

  34. Hi Experts,

    Reaching out to seek for your kind support.
    I want to count the total unique numeric value in a columns that consist of different duplicates, each value should represent"1" count

    result for below column should be 6.

    Column B:

    1441
    1441
    1441
    80
    987
    987
    11
    11
    900
    540
    540

    many thanks!!

  35. 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

    1. 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.

  36. 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

    2. 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.

  37. 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.

    2. 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.

  38. 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

  39. 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

  40. 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

      1. 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.

          1. Thank you so much

  41. 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

  42. 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

    1. 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.

      1. 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

  43. 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)

    1. 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.

  44. 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???

    1. 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.

  45. 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?

    1. 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.

  46. 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?

  47. 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

    1. 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.

  48. 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 ?

      1. 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 ?

        1. 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.

  49. 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.

    1. 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.

  50. 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.

    1. 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)