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 23. Total comments: 1074

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. 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. :'(

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

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

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

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

  22. THANX YAR

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

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

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

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

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

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

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

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

  31. hi svetlana,
    pls i need help about COUNTIF or COUNtBLANKFunctions
    How to count more blank cells separately from date for example 22.09.2015 we have 4 blank application . i trayed this but not work- =COUNTBLANK(S16:S39)-COUNTIF(S16:S39,B43 or =COUNTIF(S16:S39,""&"*")

    22.09.2015
    22.09.2015
    22.09.2015
    22.09.2015
    22.09.2015
    23.09.2015
    23.09.2015
    28.09.2015
    28.09.2015

  32. Hi Svetlana hope you can help me.

    JOHN 6
    JOHN -5
    BARRY 10
    BARRY 9
    BARRY -7
    ZACK 3
    ZACK 4
    ZACK -8
    ZACK -10
    JIM 1
    JIM 2

    Can i make a formula where the result is john 1 , berry 12 , zack -11 , jim 3.

    so it needs to look at the first and see how many johns there are and then calculate all the numbers john has.

    regards,
    john

  33. Hi Svetlana, how would i do countif for a scenario like this?

    10
    20
    80
    100 210
    10
    20
    200 30

    the countif formula should check the anything <=100 and add it.
    like answers should be 210 and 30..the values of first 4 cells are added and next 2 cells are added...Thank you...

  34. Hello Can You please help me
    A B
    1,1500 1600
    2,1300 1300
    3,400 300

    AB i want B3 in Red color
    this must done in repeated columns

  35. Hello,

    I want to COUNTIF a range A:B that is less than zero and out of that I want to pick how many are from Paris?

    I can do the first part and count how many are less than zero but how do I pick from that how many are equal to Paris?

    I seem to be able to do 2 elements in a statement but cannot add a third?

    Carol.

  36. I have 10 columns (1 row) of data like below:

    A|B|C|D|E|F|G|H|I|J
    1|0|7|5|0|1|2|3|5|4

    I need to form a new colum with triplets of numbers from each row,
    like this:
    A |
    107|
    075|
    750|
    501|
    012|
    235|
    354|
    Number 107 is formed of each separate value from cels A,B,C
    Number 075 is formed of each separate value from cels B,C,D
    Number 750 is formed of each separate value from cels C,D,E
    .
    .
    .
    etc.
    Can enyone help me to do it?

  37. Hi there -

    I am looking for help.

    I need a formula that will count the number of times "Strongly Agree" appears in column F only if there is a number greater than 0 in column AF
    I tried using multiple countIF functions as well as trying a Vlookup and am hainv no luck.

  38. Hey Svetlana!

    Well done! I can see you have been providing answers to people for over a year.

    Have a wonderful day :)

  39. I have 10 columns (1 row) of data like below:

    A|B|C|D|E|F|G|H|I|J
    1|0|7|5|0|1|2|3|5|4

    I need to form a new row with triplets of numbers from each column,
    like this:
    A |
    107|
    075|
    750|
    501|
    012|
    235|
    354|
    Number 107 is formed of each separate value from cels A,B,C
    Number 075 is formed of each separate value from cels B,C,D
    Number 750 is formed of each separate value from cels C,D,E
    .
    .
    .
    etc.
    Can enyone help me to do it?

  40. Hi there,

    Pls help me with this:

    Trying to form separate column from array like this:

    A B C D F G H I J (Excell rows)
    1 2 3 2 0 2 4 3 7 (value)

    A B C D E
    123 (New value)
    232 (New value)
    320 .
    202 .
    024 .
    243 .
    437 .

    Thanks in advance.

    1. Hello George,

      I'm sorry, it's a bit hard for me to understand your task. Could you please explain it in more detail? 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.

  41. Hi Svetlana,
    Pls help me
    I want to count "o" in J2:PB2 when E2 is greater than 8 (E2>8) .

    Thank u :)

  42. hey there, i wanted to sum or count a list, excluding some specific names. Like there is a fruit list and wanted to count/sum excluding apples and mangoes.

    i have five brands in the sales list A/B/C/D/E, want to sum only A/D/E

  43. hey there, i wanted to sum or count a list, excluding some specific names. Like there is a fruit list and wanted to count/sum excluding apples and mangoes.

  44. Hello please am trying to play around to see if i can use COUNIF or COUNTIFS to make my formula to count P and PS as one and the same thing in my totals. Thank you so much for the rapid response

    1. Hi Silvester,

      You can add up 2 COUNTIF functions, like this:

      =COUNTIF(range, "P") + COUNTIF(range, "PS")

  45. =COUNTIF(I:I,""&"")
    Column I contains formulas that return "", or a message, the message may vary. I am trying to count the messages, but the count returned is of cells containing a formula.

    e.g. the formula ="" put in a cell will increment the count

    This is in Excel 2013

  46. Nice article, thanks!
    I have two columns of data like below. I need to count the number of times the value in column A is greater than the one in column B. Is this possible using COUNTIF?

    A | B
    2 | 1
    1 | 2
    3 | 0
    2 | 1

    Result A > B: 3 times
    Result B > A: 1 time

      1. Thanks, that works great! This is the formula I’m using (in Google Sheets):
        =ArrayFormula(SUM((Data!B2:B1000 > Data!C2:C1000) * 1))

  47. Hi need to calculate how many times for particular month my in time is 9:30, 9:40, 8:20,10:20,9:50,..........in month end 9:50. Please suggest formula for how
    Many days I have come late my office time is 9:00.

  48. Hi Svetlana,

    I have a table that looks something like this: (0 is blank)

    A1 B1
    high run
    0 run
    0 run
    high run
    0 run
    0 run
    0 run
    0 run
    0 run
    high run

    I want to count the number of "run"s that occurs in the cell +1 column and -1 row of the cell with the value "high". So in this example, the return value would be 2.

    I am only able to count for one instance rather than the whole column. Can you please give any insights on writing the formula for this problem?

    Thank you

  49. Hi Svetlana,

    I have a table that looks something like this:

    high run
    run
    run
    high run
    run
    run
    run
    run
    run
    high run

    I want to count the number of "run"s that occurs in the cell +1 column and -1 row of the cell with the value "high". So in this example, the return value would be 2.

    I am only able to count for one instance rather than the whole column. Can you please give any insights on writing the formula for this problem?

    Thank you

  50. I've got a table of data that I have then created a number of concatenated columns from. From example, in my data, I have lists of contract numbers, and the month in which those contracts were processed (in some cases, this is across more than one month). I have therefore added a concatenated column that combines the contract number and the month number (1 for January, etc).

    I want to be able to find out the number of times each contract has occurred within each month so I have tried a simple countif formula that says =COUNTIF(A:A,C1). Column A contains the concatenated column and C1 is where I have the contract number and month number also concatenated for the countif to be based on. However, I get a zero every time even though I know that combination of contract and month number appear three times.

    Is my problem because all parts of the formula are referring to concatanated fields?

    Thanks for any help provided - it should be so simple but there must be some sort of limitation in Excel that is holding me back.

    1. I should add that the maximum length of the concatenated contract/month field is 10 digits.

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