Comments on: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique

The article explains Excel COUNTIF function, provides a number of examples and warns about possible quirks when using COUNTIF with multiple criteria and specific types of cells. Continue reading →

Comments page 22. Total comments: 1074

  1. I have a column of UPS, FedEx, and DHL tracking numbers. I am trying to sum up the total # of tracking numbers in that column. How can I do this when the tracking number has both text and number context in it?

    e.g.:
    36006962160000026558847083172014
    1Z9V14091300013079
    7614784914

    1. Have you tried using the following code?:
      =COUNTA(A1:A3)

  2. Hi Svetlana

    Following on from post 213, I have modified the formula slightly and used it as part of conditional formatting via the "Formula is" new rule and it works perfectly. Is there any way that the identified duplicate rows that meet the formula can be copied to a new sheet next available row? The formula is = AND(ISNUMBER(A1), ISNUMBER(B1), C1="", D1="",COUNTIF(E:E, E1)>1) any help or direction would be much appreciated.

  3. I'm trying to calculate only the number of cells in which the employees start date is less than 90 from there last day worked. These dates are captured on two separate columns for multiple employees. I can't seem to figure this one out.

    Your help is greatly appreciated.

  4. I have the following data:
    Case Rate
    A 0
    A 2.58
    B 0
    B 0
    C 13.45
    C 0
    C 0
    D 0
    D 0
    D 0

    I need to determine how many of the case groups have a total of Zero. In the above example, it should read 2 case (Case B & D) arethe only one that has a zero total

    1. Note - the case letters are only for this example, usually it will be different names

      1. I have tried the following formula but it doesn't produce the results that I am looking for: =COUNTIFS(A2:A16,A2:A16,B2:B16,0)

  5. Thanks a million, answered all the questions that Excel-help couldn't give me (particularly on how to count dates separately from text).

    Keep it up!

  6. Example 1. Find and count duplicates in 1 column

    For example, this simple formula =COUNTIF(B2:B10,B2)>1 will spot all duplicate entries in the range B2:B10 while another function =COUNTIF(B2:B10,TRUE) will tell you how many dupes are there:

    Good Day!

    May I know what is the function of "B2" in this example >> =COUNTIF(B2:B10,B2)>1 ?

    Thanks a zillion!

  7. Hi, I want to count all cells that have numbers only. I have some cells that have NA and I want to exclude those from the calculation.
    thanks,
    Di

  8. Help needed... I am trying to read number of cells which match "check" in another excel document and count if formula is not working correctly.I have a document "X1" at "C:\Users\XY123\Documents\Work\" which I named as sheet1_XLSX(C:\Users\XY123\Documents\Work\X1.xlsx). I am using this formula to open and do countif function like this : =Countif('[sheet1_xlsx]sheet1_xlsx.xlsx'!$E:$E,"check"). But the formula is returning as #value!.

  9. hi Svetlana, i have created an any year calendar and i want to mention holidays, here in Sri Lanka, the holiday will differ every year, so how i can display holiday by manual for every year?
    Thawfeeque.

  10. Hi Svetlana, I'm trying to identify duplicates in a column based on values in adjacent cells. For example:
    A B C D E
    375 500 Cheddar (D)
    Soft Brie
    125 Hard Cheddar
    125 375 Cheddar (D)
    375 Soft Brie

    A & B are number format; C, D and E are Text format.
    For a duplicate to be correct A & B must have numbers present, C & D must be blank - I have indicated the duplicates here with a (D) but not needed in the formula. If I had hair I would have lost it today trying to figure this out - please help.

    1. Hi Roy,

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(AND(ISNUMBER(A1), ISNUMBER(B1), C1="", D1=""), IF(COUNTIF(E:E, E1)>1, "dupe", ""), "")

      1. REF: 213
        Hi Svetlana many thanks for the quick reply. I was just venturing in to the ISNUMBER function when your solution arrived. Yes it works thank you, however, it doesn't wait for a comparison before stating "dupe" - my fault for not being very clear. Unless I'm mistaken ISNUMBER only caters for a single cell and not a range - is there a way around this please?

        1. Hi Roy,

          Yes, ISNUMBER accepts only an individual cell in the argument. Sorry, I do not quite understand what you mean when saying "it doesn't wait for a comparison before stating "dupe". Please specify what cells should be compared.

          The formula I suggested works with the following logic:

          If A1 & B1 have any numbers in them, and C1 & D1 are blank, check column E for duplicate values (i.e. check if the value in E1 occurs in any other cell in column E). If one or more duplicates are found, the formula returns "dupe", an empty string otherwise.

          1. Hi Svetlana
            Essentially, when I re-read what I have asked of you, you have responded by producing exactly what I requested - my apologies for the lack of clarity. To keep it its simplest terms, I have a range of cells A1:E20 where a duplicate condition is met when a row has A1 & B1 with numbers in them and both C1 & D1 are blank, E1 would then be checked for duplicate values. However, on the first occasion that this occurs it cannot be considered a duplicate it is a unique value until a second occurrence appears etc. I have been trying COUNTA in place of ISNUMBER, still not quite there but I think it's the last bit that might be the key.
            Once again apologies for the confusion, I really appreciate what you and the team at Ablebits do in supporting us lesser mortals.

            1. Hi Roy,

              No need to apologize. From my own experience, it's very difficult to exactly explain/understand the task without seeing the same source data :)

              Please try the following formula. It identifies duplicates without 1st occurrences:

              =IF(AND(ISNUMBER(A1), ISNUMBER(B1), C1="", D1=""), IF(COUNTIF($E$1:$E1, $E1)>1, "dupe", ""), "")

              Is this what you are looking for?

              1. Yes Svetlana - works a treat - great job - have a good weekend!

  11. I have a worksheet that contains all my sales of various products to various customers over several weeks.

    Assuming column B lists the customers, column c lists the products, and columns f to z lists the sales volume sold each week, how can I determine the number of unique customers (col b) that purchased at least 1 of any product in any given week (col f to z)... given that customers could purchase multiple products in any week and be listed multiple times in col b?

    Thanks

  12. Hi Please im begging for your help on this please.

    i have a problem with countif. how will i count Arriola with more than 2 days? i dont want to count arriola i want to count only arriola with more than 2 days.. please

    A B MORE THAN 2 days
    Lionel 2 Lionel = 0 << LIKE THIS RESULT
    Lionel 2 Arriola = 1
    Arriola 3
    Arriola 1
    Lionel 2
    Arriola 2

    1. Hi!

      To count cells with 2 or more criteria, you need to use the COUNTIFS function.

      Supposing that "Arriola" is in column and the number of days in column B, you can use the following formula:
      =COUNTIFS(A:A, "Arriola", B:B, ">2")

  13. A B
    1 Grn:1 11-01-2016
    2 Grn:2 11-01-2016
    3 Grn:2 11-01-2016
    4 Grn:3 11-01-2016

    in this table i want to count in 11-Jan-16 how many GRN I received it should not count double like GRN:2 i received 2 time in same date so i want to count only one time.

    1. Hello Sunil,

      I recommend adding a column that will check if the values have duplicates. Then you can use the COUNTIFS function to calculate the result by two criteria.
      https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/

      Please see a sample file with the functions that I described:
      https://support.ablebits.com/blog_samples/excel-countif-examples_210.xlsx

      Our Excel add-in that allows to remove duplicate rows can also be helpful for you:
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

  14. I am having one doubt regarding counting value like

    A B
    1 Grn:1 11-01-2016 in this table i want to count in 11-Jan-16 how
    2 Grn:2 11-01-2016 many GRN I received it should not count double
    3 Grn:2 11-01-2016 like GRN:2 i received 2 time in same date so i
    4 Grn:3 11-01-2016 want to count only one time.

    Please help

  15. Hi Svetlana,

    I am trying to calculate the number of countries with a life expectancy between 50-55,55-60,60-65...etc.
    columnA: country name
    ColumnB: Average life exp.

    would i be able to use the =countif function to find he amount of countries with the given criteria?

    1. Hi Noah,

      To count with multiple criteria, you need to use the COUNTIFS fucntion. For example:

      =COUNTIFS(B:B, ">=50", B:B, "<=55")

      1. So how do you drag it down to reflect the count of the changing range without manipulating the individual cells manually.

        For eg. 50-65
        And next cell will be 70-75, etc

  16. Hello Svetlana

    I have a spreadsheet where i have to count the amount of unique rows in column C that had the value "UDL" in column N.
    Column C can have duplicates so somehow i need to find out how many unique ones there are

    1. Hello Peter,

      You need the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM(IF( (--($N1:$N31="UDL"))*(--(COUNTIF($C$1:$C$31,$C1:$C31)=1)),1,0))

      With row 1 being the first, and row 31 being the last.

      Uniques imply that there is only one occurrence of the value in column C.

      If you need unique values plus the first occurrences of the duplicates, then you need to use a Helper column, e.g. column O, and enter the following formula into its first cell:
      =IF(COUNTIF($C$1:$C1,$C1)=1,"Unique","Duplicate")

      Paste the formula to all cells below.

      Then use the following formula to count:
      =COUNTIFS(N1:N31,"UDL",O1:O31,"Unique")

      You can also use the Helper column to count only uniques, then the initial formula should look the following way:
      =IF(COUNTIF($C$1:$C$31,$C1)=1,"Unique","Duplicate")

  17. Sorry for question.
    found answer as COUNTIFS(A1:A1001,1,B2:B1002,2).
    Thank you anyway.

  18. i have columns of headings standard MHRs and Actual MHRs I want to count the values in columns heading std mhrs.

  19. Hi,
    I wonder can you help,please
    When I use COUNTIFS(A:A,A7,B:B,B2) this reads as doubles on same line no matter what the starting numbers.I use this in Lotto results and would like it to read from two different results,in stepwise format A:A,A1,B:B,B2,or is there a better way to do this,thank you in advance.

  20. A B

    1 Product Loan Range

    2 Salary Loan <250
    3 Salary Loan <250
    4 Home Mortgage 250 < 350

    Now i want to count how many salary loan under "<250"

    =COUNTIFS(A2:A4,"Salary Loan",B2:B4,"<250")
    Here am getting answer is zero. Since it is counting less than 250. But the Salary Loan under loan range "<250" counts 2 times.
    I solved this issue by converting Range B to value 250,250-350,..

    Is there any way to count without changing the format in Range B.

  21. Hello

    I have a question regarding a spreadsheet I am putting together and I am unsure if using counts would be the best way.

    ITEM # | TRANS CODE | DOC DATE
    AA R 10/1/15
    AA R 10/1/15
    AA R 10/2/15
    AB R 10/1/15
    AB R 10/1/15
    AC R 10/2/15

    In the above example I have three part numbers, AA, AB, and AC. Each one is a receipt on the day in the last column.
    I am trying to count the number of receipt days in a week for each part, not just the number of receipts per day.
    So for the AA I would have two receipt days (10/1 and 10/2), for the AB and the AC I would have one receipt day.
    If I were counting the number of receipts would results in AA:3,AB:2,AC:1
    My problem is I can not figure out how to do the code so that way when I type in the part number it returns and then sums the number of receipt days and not the number of receipts.

    1. Hello Jess,

      I recommend adding a column that will check if the values have duplicates
      =IF(COUNTIF(B6:$B$11,B6)=1,"no duplicate","has duplicate below").

      Then you can use a Pivot Table to count filtered values.

      Please see a sample file with the functions that I described:
      https://support.ablebits.com/blog_samples/excel-countif-examples_202.xlsx

      Our Excel add-in that allows to remove duplicate rows can also be helpful for you:
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

  22. Hello,
    Example: =COUNTIF('C:\Users\Desktop\[test.xlsx]List1'!$B$1:$B$10;B11)
    Why when I close test.xlsx file this function returns #Value!# error?
    (for other functions a link is not problem)
    thank you for your answer, really :)

    1. Hello Lukas,

      Unfortunately I haven't found a way to get the formula re-calculated correctly without an open book.

  23. halo i using COUNTIFS function but when i use it it appear #VALUE!
    i just only write this

    Location(title) JAN FEB
    PgP1 ( WRITE HERE )
    Hcp

  24. Hello Mr Svetlana, i'm a doctor and i'm trying to figure out how to organize my colleagues data shift. I tried to use the "countif" formulas and it worked fine for normal shift that has to count 1 (stands for 1 turn of 6,3 hours) but i would like that excel will count a night shift as 2 (because the colleague that appear in the night line one time does 2 turns because he/she remains for 12 hours. How can i do that?
    Thank you very much

    1. Hi Paolo,

      It's difficult for me to suggest an exact formula because I don't know how you identify night sifts. But you can use the following approach:

      =COUNTIF(A:A, "night shift")*2

  25. Hi!
    I have a list of approximately 12000 rows with data. Column A has unique IDs, while column B has a date. How can I remove duplicate rows based on identical ID, only when there exist another date within 60 days of the first post?
    123456 12.09.2015
    123457 13.09.2015
    123456 15.10.2015
    123458 25.11.2015
    123456 12.12.2015
    In the example above, I want to get rid of the third line as it is within the 60 days from line 1. However, I want to retain line 5, since that is more than 60 days after line 1. Is it at all possible?

    Sincerely,
    Christoffer

    1. Hello Christoffer,

      I recommend you to add a combined column in the following way:

      ID Date Combined
      123456 9/12/2015 123456 more 60
      123457 9/13/2015 123457 more 60
      123456 10/15/2015 123456 more 60
      123458 11/25/2015 123458 more 60
      123456 12/12/2015 123456 less 60

      Please see a workbook with an example here:
      https://support.ablebits.com/blog_samples/excel-countif-examples_198.xlsx

      Then you can use our Excel add-in that allows you to remove duplicate rows.
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

  26. Hi Svetlana,

    Would it be possible to count a number of cells that contain numbers with letters? such as a list of number plates?

    AV63 OEB
    AV63 OEM
    AV63 OEN
    AV63 OER
    AV63 OES
    AV64 PYH
    AV64 PYW
    BJ13 LZW
    BJ13 MGE
    BV13 CZH
    BV63 CGF
    CK13 DKJ
    FD13 KYJ

    Thanks!

  27. I am counting rows that have unique values and also match multiple criteria, but the formula I have is coming up a view short each time. Using a helper worksheet on the same tab does not help:

    I4 refers to a Warehouse ID = say "2A", and I need to count the unique bin locations referred to in E2:E7111. So for a specific warehouse, i need the number of unique bin locations in the array for warehouse 2A.

    {=SUM(IF($A$1:$A$7111=I4,1/COUNTIF($E$2:$E$7111,$E$2:$E$7111)))}

    Appreciate your eyes on this to see if I'd doing something wrong.
    Thanks
    David

    1. Hello David,

      If possible, please send a sample spreadsheet with the way your data and the expected results look to support@ablebits.com. Please include the name of the blog post and your comment. We'll do our best to assist you.

  28. Hi Svetlana
    Can I have a tab number in my countif formula e.g. =COUNTIF(C3:C46,"*C48*").
    I am essentially trying to have a tab (in this case c48) that I can change the request in.
    So for example if I change the content in c48 to OSS - it will count how many time OSS appears in my cell range of c3:c46.
    Then if I change c48 to World - it will count how many times world appears in my cell range.
    Thank you in advance

  29. Hello
    Can somebody help me in doing this in excel. I have a bunch of rows with 8 numbers in each row. I want to compare the numbers in the first row with the numbers of the second row. Then it gives me a count of how many numbers are the same. Then we move on to compare the numbers of the first row with the numbers of the third row and again it gives me a count of how many numbers are the same AND adds it to the result of the first comparison. …. And so on till we end up to the last row(i.ei 10th row).
    For example:
    Row 1 2 5 7 9 10
    Row 2 6 4 2 11 5
    Row 3 6 9 12 2 7
    Row 4 8 1 4 11 6

    Comparing the numbers in row1 to the numbers in row 2 results in 2 as the numbers 2 and 5 are in both rows. Then we compare the numbers in row1 with the numbers in row3 which will results with 3 as the numbers 2,7,9 are in both rows. Hence the new returned value will be 2(result from first row checking)+3(result from second row checking)=5
    Then we compare the numbers in row1 with the numbers in row4 which will results with 0 as there are no matching numbers between the row1 and row4. Hence the new returned value will still be 5(result from first row checking)+3(result from second row checking)+ 0(result from forth row checking)=5

    Now we start comparing the numbers of row2 with the numbers of the other rows and so on

    thanks

  30. I'm trying to count how many "Yes" in I2:I322, only if D2:322 equals "SSA"

    =COUNTIFS(USA!D2:D322, "SSA",USA!I2:I322, "Yes")

    It's only counting a subset of all the "Yes"s that qualify.

    1. I figured out this formula is working. The problem is that it seemed it was wrong because of filtered rows. So, please ignore this question. Thanks :)

  31. svethlen mam..

    am going for analysing data in spreadsheet which is taken from rating questionair data. here is data like.

    a
    b
    c
    d
    c
    b
    d
    d
    c
    a

    in want konw how many A.B.C.& D ' s in this coloum.. plz sugest me a formula for this problem...

  32. Hi, I use =SUM(1/COUNTIF(M3:M100;M3:M100)) formula for calculate name in row and it is working without blank cell. but in this range I have some blank cell and formula is not working. Please help

  33. Svetlana,

    Is there any way to use an iteration of the countif fuction to match words on two different columns and give a total, but exclude if that specific word is on both columns.

    Example Below. There are 4 columns A,B,C,D and if i use the countif and look for june on column A, it will give me a total of 1 which is found on A3.

    What I want is a countif formula to look at column A and column C, and look for the word June and give me a total, but exclude from the total if the word june is in both columns, which would be 2 since C1 and C5 only show up once on both columns and A3 and C3 is exclude since they appear on both.
    A B C D
    1. march 2 June M
    2. april T March 1
    3. june 3 June G
    4. july y july 6
    5. May 5 June 4

  34. =IF(G8-F8,"Partial","Full") - I want to add another text. Can help?

  35. Let me ask u one .
    what is the formula to count the redundancy of the value in a single cell with the range time?
    e.g how many times , i write "N" In cell A1 From 2:30 to 2:45

  36. Hi Svetlana,

    I have a range of cells from F8:F65,to DB8:65 all with Values like "Pass" and "Fail". I want to capture the results of Pass in a formula using COUNTIF. My example is =SUM(COUNTIF(F8:F65,"Pass"),(COUNTIF(G8:G65,"Pass"),(COUNTIF(H8:H65,"Pass"))). But this is not working. It is working till the G8:G65, but later it is not working.

    I know there are 5858 "Pass" in this range but want to capture this information in a cell without having to hardcode this.

    Thanks and Regards,
    KM

  37. Hi, I want to filter the value for two column. The column has 3200 rows, same goes to the second column. So, now I want to filter the number of row for column A that have greater value than column B. Please, i really need your help. :'(

  38. Hi

    I have looked at the responses above but I can't see anything close to what I need. I have 3 columns. the first has office names and the second has PO numbers, the third has dates. The second column has unique and duplicate PO's. I would like to count the amount of uniquem then duplicate PO's from a certain office in a certain date range. i.e Cardiff between 1-2-15 and today.

    Thanks

  39. Hi Svetlana,

    Quick question on how to use the countif/countblank.

    I have an excel spreadsheet where I need to determine the number of items in a column that are blank so I am using =countblank (A1:A94) but I have to continually change the range because if I do =countblank (A:A) I will get too many results.

    So my question is which formula could I use to count blanks in all of A if column B = Yes

    1. I figured it out after browsing the site some more.
      For those wondering; here is the formula

      =COUNTIFS(A:A,"",B:B,"YES")

  40. i have a table with "count" formula on one column then an "expired" word on the other, i was thinking if there's a way which i could see that the number of "expired" units will be equal to the number as have been counted. wish i could have shown you my table. tnx

  41. please help me i need to pass or fail a learner in a row from C6:O6, by getting 3x30s and 3x40s

  42. THANX YAR

  43. Hello, i'm trying to count the results to a survey i conducted and i need to graph very specific things.

    What i want to do, in English, would be like:

    If (column C)= Female, count how many times the word "weapons" is in column G

    I don't know if it's there in the examples and i can't see it because i'm not used to Excel, or if it just can't be done.

    Thanks for the tutorial though! I'm going to use some other things.

  44. What do i need to do to count data that has a date less than today, but only count as far back a 2 weeks?
    =countif(A23:a,""today -14??

    1. Hi Matthew,

      To count with 2 or more conditions, you need to use the COUNTIFS function:
      =COUNTIFS(A1:A20, "<"&TODAY(), A1:A20,">="&TODAY()-14)

  45. Dear Lidiya,
    Can i calculate 00:01 to 00:59 mnts Greater than equal to 1 and 01:00 to 9:59 Greater than equa 2 how can i do it

    thank
    k sahoo

  46. plz give me a example i can not under stood

  47. Dear Mam/sir
    Can i calculate 00:01 to 00:59 mnts equal -1 and01:00 to 9:59 euql 2 how can i do it

    thank
    k sahoo

  48. Hi there - I am close to my solution but am stuck with my function. I have values of 1-7 that each number represents a certain type of "fail" for my employees in their audit. Each row in my sheet represents an employee and in their corresponding cell, there might be multiple reasons for them to fail an audit and sometimes the failed reason could occur twice in their audit. So, if Ben failed an audit only because of "insufficient financial information", then he would get a "1" in his corresponding cell, (where 1 corresponds to that particular fail). However if Donna failed due to "insufficient financial information" (1) and "MD referral guidelines not followed", (which corresponds to number 6)then on another case failed due to "1" again then she would have a "1,6,1" in her corresponding cell. So I have employee names in column A, then the fail results in column B. Then I have D1 labled "Fail Type" and E1 though K1 labeled 1 through 7. Then D2 labeled "count of Type" and starting with E2 through K2 I have the formula =COUNTIF($B$2:$B$5,"*"&E$1&"*")+COUNTIF($B$2:$B$5,E$1) in each cell - it will only count Donna's two "1's" in her cell, once. I need to have it count those both times they occur in Donna's so that in E2, it shows "3" instead of "2" as it does currently.

    1. I'm sorry, it's a bit hard for me to understand your task. Please send a sample spreadsheet with the way your data and the expected results look to support@ablebits.com. Please include the name of the blog post and your comment. We'll do our best to assist you.

  49. Hi, I am working from a formula on Excel's Student calendar template. The current formula is =IF(LEN(B14)=0,"",IF(COUNTIF(Assignments[Month],DATE(WkYear,WkMonthNum,B14))>0,"Assignment due!",""))
    I would like to change the "true" value from "assignment due!" to whatever the contents are in the cell is that it found in the look up (i.e the name of the assignment).

    I Can't figure out how to make that happen- can you assist?
    Thanks for your help!

  50. Hello,

    I just used your countif suggestions and think I'm doing something wrong because I'm not getting the correct value.

    Here is the formula I used:
    =COUNTIF(NexTrak!B2:B130, "Bariatric") + COUNTIF(NexTrak!C2:C130, "Washington")

    However, instead of telling me the amount of clients in the Bariatric specialty in the state of Washington, it is adding them (most likely bc of the +). How would I use the Countif formula as a type of conditional formula. I actually only have 1 Bariatric Client in Washington and 39 Clients total in Washington.

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