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

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

  2. THANX YAR

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  18. Hey Svetlana!

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

    Have a wonderful day :)

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

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

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

    Thank u :)

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

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

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

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

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

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

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

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

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

  31. Hello;
    I'm looking to set up a formula that will count all values in column A, when column B has a date of 2012?
    Thank you!

    1. Hi Grace,

      If you want to sum the values in column A, then you can use a formula similar to this:
      =SUMIFS(A1:A6, B1:B6, ">=1/1/2012", B1:B6,"<=12/31/2012")

  32. Hey, i need your help for

    Col 1 has Unique id ( Emp Code) and col 5 has date.

    i need to find out whether a Emp Code has Visited for more then once in a days.

    exp emp code 101 and Emp has Visited for more then twice a days in 2 place.

    need to make report regarding Emp Visited on tht day

  33. Please help me. I'm stuck on the following excel formula:

    =IF($H$26="Acting","","",IF($H$26="Temporary","","",IF($H$26="PERMANENT"(VLOOKUP($D$20,'Job Title'!$A$2:$C$28,3,FALSE),""))

    “Acting”, “Temporary”, or “Permanent” are text
    If H26 is “Acting” or “Temporary” return should be blank
    If H26 is “Permanent” it should perform a VLookUP at different D20 and compare it to spreadsheet named Job Title.

    Getting error: "The formula you typed contains an error"

  34. Hello,

    I have a simple two column worksheet. Column one consists of names, and column two of dates those names were given a task. I need a total that calculates how many times each name was given a task on today's date.

    So each day, as the tasks are assigned and entered the total should reflect how many were given to each name that day.

    I am trying to use a countifs formula. I have B:B as the Criteria_range1, and =COUNTIF(B:B,TODAY()) as Criteria1, but this doesn't work.

    Can somebody tell me what I need to enter to make this calculation work? Any help would be greatly appreciated.

    1. Hi Douglas,

      The formula is correct and it worked perfectly on my test sheet. I can think of only reason for it not working - dates formatted as text. Is it the case?

      1. I currently have column B formatted as Date, and chose the 03/14/01 format. :(

        1. Thanks for the help Svetlana. I'm sure it is something minor I am overlooking, but I can't figure it out. I have tried formatting column B as text, date and custom but still cannot get it to work.

          1. Douglas,

            Minor things often cause big problems :) If you can send your sample workbook with the formula to our support team (support@ablebits.com) we will try to figure it out.

            1. Svetlana, thanks, that did the trick!!!! You're amazing, and this site is wonderful! Thanks again!!

  35. Nice Article!

  36. I am trying to count occurrences above a certain number; however, those values I want to count are not in a straight row or column. I have each company, then label, then each month in the columns. For each company in the column, I have 4 sets of information. I want to count 1 set for each company. How do I do that?
    Sample:
    Company A $ of x $50
    $ of y $12
    # of employees 49
    Company B $ of x $150
    $ of y $75
    # of employees 289

    For each company, I want a count of any time the number of employees in that column is greater than 250.

  37. Hello,

    I am trying write a function to count the number of unique values in one column that fall in a category in another column.

    Here is a simplified example of my data:

    Order Qty Group
    1 1 1
    2 2 1
    3 1 1
    4 4 1
    5 5 2
    6 8 2
    7 2 2
    8 4 2
    9 6 2

    I want to know how many different/unique order quantities there are within group 1, group 2, etc but without designating the cell range, just the particular value (1 or 2 in this case).

    Thanks so much!

  38. ________________
    |__a_|____b_____|
    |poo |480 menit |
    |ssp |360 menit |
    |LLLL|300 menit |
    |LLLL|300 menit |
    |____|__________|

    so the the result would be like this :

    ________________
    |__a_|____b_____|
    |poo |480 menit |
    |ssp |360 menit |
    |LLLL|600 menit |
    |____|__________|

    how to do opperation like that ? thanks before

  39. Hi Svetlana,

    I have a problem I think can be solved using this method but I cant seem to get the syntax right

    I have a list of words in 1 column that have duplicates and a date range in another column

    What I need to do is count non duplicates when the date is less then 7 days ago.

    the table would look something like this

    Apple 26/6/2015
    Apple 24/6/2015
    Pear 20/6/2015
    Pear 15/6/2015
    Pear 22/6/2015
    Orange27/6/2015

  40. Hi Svetlana,

    I am using excel 2003 :(

    I am trying to count how many cells in column D contain a specific text, IF the dates in column F are greater than 6 months ago from today.

    I have tried this:

    =SUMPRODUCT((D4:D16390=D4)*(F4:F16390="<="&TODAY()-180))

    But cant seem to get it to work, just shows 0.

    Many thanks!

    1. Hi Gary,

      Try the following array formula:
      =SUM((D4:D16390=D4)*(F4:F16390<=TODAY()-180))

      Remember to press Ctrl+Shift+Enter to complete it.

      1. Hi Svetlana,

        Perfect! Thank you very much for that!

  41. Hi,
    I need to count the amount of profit-making enterprises:

    Проект* Выручка Себестоимость
    SAMA-001_ALFA 100 90
    SAMA-003_BETA 120 200
    ...

    THANK YOU

    1. Count #projects if = Выручка>Сеьестоимость

      1. Hi,

        Supposing that "Выручка" is column B and "Себестоимость" is column C, you can use the following array formula:

        =SUM(--(B2:B10>C2:C10))

        Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

  42. Can you please give me solution for my earlier email?

  43. Please help me to solve my assignment I dont know how...
    Count if
    A B C
    1. 10 20 15
    2. 5 25 4
    3. 3 6 2

    Count if
    1) 5-2
    2) 1-5
    3) 15-25

    1. Hello Nikki,

      Here you go:

      1) 5-2 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&2)

      1) 1-5 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&1)

      1) 15-25 =COUNTIFS($A$1:$C$3, "<="&25, $A$1:$C$3, ">="&15)

  44. Hi Svetlana,

    I have some data like this in a column. If am giving formula (=COUNTIF(A1:A2000, A1)) to count the number of repetition of each number its not getting counted properly. Its taking into con sideration only few entries. May be coz character are more than 19. Is there any way to count the repetitions if the number of character in a cell is more than 19.

    89XXXXXXXXXXXXXXX44
    89XXXXXXXXXXXXXXX36
    89XXXXXXXXXXXXXXX28
    89XXXXXXXXXXXXXXX10
    89XXXXXXXXXXXXXXX02

  45. Hi Svetlana,

    Looking for an idea for a function. I'm pulling data from one spreadsheet with account numbers assigned to different agents, and trying to compare it to the number of entries made in our sales database for these accounts. The problem is, the database entries contain the account numbers, but also contain other text/numbers as well. Any suggestions? Thank you!

    1. After tinkering around with it, I think the main point of my problem is getting the *value* function to work with a cell reference as the value, rather than a constant.

  46. Hi,

    Can you help me out in finding the hours in an column G which is >4 <6 and =6.

    time is in 4:00:00 formate and also i need to find no 4 to 6 in same column

    Thanks
    Shwetha

    1. Hi Shwetha,

      You can use the TIME function in your criteria, like this:

      Times greater than 4: =COUNTIF(G2:G100, ">"&TIME(4,0,0))

      And use analogous formulas for other calculations.

  47. Hi,
    I know how to do this using PIVOT, but I have a non power user that will use a template sheet in excel and I'm trying to help him to get a count duplicates cases based on a condition.

    Case Cause Dealer
    447839 L CA
    447839 P CA
    447839 S CA
    448387 L CA
    448387 P CA
    447646 L PH
    447646 P PH
    447647 L PH
    447647 P PH
    447648 L PH
    447648 P PH

    In above example CA have 2 cases and PH have 3 cases. Using PIVOT is easy to get the result rather then calculating through transactional data but the user isn't PIVOT oriented and I tried hard to trained.

    How I can achieve above summary results using a function/complex formula.

    Anticipate thanks,

    Jose

  48. Hello,

    I have the following data:

    Column A Column B Column C
    Estimate Percentage Percentage Expected
    (days) Complete Remaining Result

    0 0 Not Required
    5 25 3.25
    1 75 .25
    Not Required 0 Not Required

    So, those fields that are not a number and are equal to zero should not be calculated, but for the formula used to calculate "Percentage Remaining", it is: Column A * (Column B /100)

    So, my question is: How do I exclude the calculations that are zeros and are not numbers in Column A and Column B?

  49. Hi, I have a spread sheet where I have a resident name in column A and a hall number in column B. There are multiple residents on the same hall, and sometimes the resident is listed more than once. I want to count the number of times a hall is listed, but no duplicates(i.e Joe 100
    Bill 200
    Joe 100
    Chris 300
    Maple 100. So I would want to find out how many times 100 is involved without the duplicate so the count should be 2. Can you help me? thanks,Joe

  50. Hi - I have a baseball schedule and trying to see how many times each team has a early and late game. I have used the countifs but can't get it to work.
    Away Team # Vs Home Team # Time and Diamond
    1 vs 2 715 P
    3 vs 4 845 P
    5 vs 6 715 D1
    7 vs 8 845 D2
    11 vs 12 715 D1
    9 vs 10 845 D2
    12 vs 1 715 P
    10 vs 3 845 P
    8 vs 5 715 D1
    6 vs 7 845 D2
    4 vs 9 715 D1
    2 vs 11 845 D2
    Thank you, Kendra

    1. Hi Kendra,

      And how do you determine whether it's an early and late game?

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