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 12. Total comments: 1067

  1. I have this in several rows of column AE

    =IF(S7=AC7,"YES","NO")
    thru
    =IF(S100=AC100,"YES","NO")

    and I want count the YES, several results are YES, so

    =COUNTIF(AE2:AE5000,"YES")

    but results=0, please help

  2. Hi,

    If i want to count the number of cells in the range B2:B10 with a date greater than or equal to the date in another range (let's say Z2:Z10). How can i change the following formula:

    =COUNTIF(B2:B10,">="&Z2-"7")

    Thanks!

    1. XP,

      Provided all the cells are formatted for date your formula should work...if you take the 7 out of those quotes...so:

      =COUNTIF(B2:B10,">="&Z2-7)

  3. Can i use the countif function to do the following: in a range of rows,i want to count the rows and then select only those rows where the value in column B differs from the value in column A.

    1. sample data please, Josh...better answers with better deets

  4. I am trying to get the highest value from a list of serial number range list
    LOOKUP(2,1/(COUNTIF(K3:K200,">"&K3:K200&"*")=0),right(K3:K200,12))

    i am looking for the max value by searching only the rightmost 12 letter in range.

    Sample data
    -----------
    K
    000112717423 - 000112783422
    000112783423 - 000112837322
    000112837323 - 000112811822
    000112811823 - 000112812322
    000112811823 - 000112812322
    ...

    expecting answer
    ----------------
    000112837322

    because i am using excel 2010, i cannot use the following code
    =MAX(VALUE(RIGHT($K$2:$K$200,12)))

    1. Hi Ken,
      This is most easily solved if you are able to insert a separate column as an intermediate step to get the value. e.g. cell L2: =Value(Right(K2,12)). This can be hidden but you need to there are enough formulas if you add new data.
      Andrew K.

  5. Thank you for sharing this information

  6. Hello,

    I am trying to count if a student's score is lower than the total points possible. Each column has the score listed in row 4, the category listed in row 3, and, in the case of the first student I am working with, the student's scores in row 5. Each column is a different day, from column D through AT.

    Right now, I have the following formula, and am getting 0 returned even though the student has partial credit on several assignments.

    =COUNTIFS(D5:AT5, "<" &D$4:AT$4, $D$3:$AT$3, "HW")

    Is it just too much to ask to compare a range of cells to a corresponding range of cells or am I making a mistake here?

  7. BILL NO ITEM NAME
    CB/00004 FROCK GIRLS
    CB/00010 FROCK GIRLS
    CB/00011 FROCK GIRLS
    CB/00011 T-SHIRT
    CB/00015 T-SHIRT
    CB/00017 T-SHIRT
    CB/00017 T-SHIRT
    CB/00017 CAPRI
    CB/00017 CAPRI
    CB/00018 CAPRI

    WE NEED OUTPUT FORTHIS FORMAT CAN U HELP ME WHAT FORMULA I CAN USE

    ITEM COUNT
    FROCK 3
    T-SHIRT 4
    CAPRI 3

    1. Hi! I am learning Excel in more depth myself and have found a way for you to display your desired result, even after adding more bill numbers and item names. In my formulas, the bill number column is not included; only the item name column and headers which I have created for formula reference. This is great, because the formulas will automatically calculate and either increase if more data is entered in column C or decrease if data is deleted in column C. However, the item names must be the same. If not, you can always add more headers and use the formulas as a reference for new items.

      Let's assume your bill number and item name column headers are in cell B2 and C2, respectively. In reference to the data you listed, your bill number data is in column cells B3:B12 and your item name data is in column cells C3:C12.

      There are 4 headers to the right of columns B and C. Cell E2: Item Count, Cell F2: Frock Girls, Cell G2: T-Shirt, Cell H2: Capri.

      In cell F3, the formula for calculating the total number of Frock Girls is: =COUNTIF(C:C,"Frock Girls") - the result should be 3. In cell G3, the formula for calculating the total number of T-Shirts is: =COUNTIF(C:C,"T-Shirt") - the result should be 4. In cell H3, the formula for calculating the total number of Capris is: =COUNTIF(C:C,"Capri") - the result should be 3. Now, for the final result!

      Remember, the Item Count header is in cell E2. In cell E3, the formula for combining the information for Frock Girls is: =CONCATENATE(LEFT($F$2,5)," ",$F$3) - the result should be "Frock 3". In cell E4, the formula for combining the information for T-Shirts is: =CONCATENATE(LEFT($G$2,7)," ",$G$3) - the result should be "T-Shirt 4". In cell E5, the formula for combining the information for Capris is: =CONCATENATE(LEFT($H$2,5)," ",$H$3) - the result should be Capri 3.

      I hope you find this useful. :)

  8. Thank you so much for this!

  9. there is lots of number 0 to 2000. i want to count how many numbers are there between 0 to 100. For that i use =COUNTIF(A1:A20,"<100"), then what is the formula i need to use to count 101 to 2000?

    1. reaz, assuming that you want to include 101 and 2000 in your count, then use =COUNTIFS(A1:A20,">100",A1:A20,"101",A1:A20,"<2000").
      Please note that your original formula =COUNTIF(A1:A20,"<100") will include any zeros, so your count will be 100, not 99

    2. Since you want numbers btn 0 and 100, it implies 0 & 100 will be left out in the formula, so the best way is to use a multiple function as;
      =COUNTIFS(A1:A20,">0",A1:A20,"<100")

  10. Thank you for sharing this information

  11. Hi,

    I'm trying to create spreadsheet that calculates student scores.

    Students get graded as either a 1, 2 or 3 which goes in a column next to their name - pretty standard.

    What i'm having trouble with is trying to count the number of students who got a grade 2 but who also tick another criteria, which is that they have English as an additional language (EAL). I have a column next to their name which simply has a Y in it if English is their additional language. So i'm tring to count: "How may students who have EAL got a grade 2?"

    I managed to get the count for those with grade 1, and 3 but when trying to apply the same formula to the 2's, i get the error message: "ErrorFunction IF parameter 1 expects boolean values. But 'Y' is a text and cannot be coerced to a boolean."

    This is the formula i used to successfully count the students with EAL that got 1's and 3's (the initial data is on a different tab called Y6):
    =IF('Y6'!D2:D33,"Y")+COUNTIF('Y6'!AF2:AF33,"1")
    =IF('Y6'!D2:D33,"Y")+COUNTIF('Y6'!AF2:AF33,"3")

    I get the error message above when i use the same formula for the 2's
    =IF('Y6'!D2:D33,"Y")+COUNTIF('Y6'!AF2:AF33,"2")

    Any idea how i can calculate these totals?

    Thanks!

    1. Use COUNTIFS instead =COUNTIFS('Y6'!D2:D33,"Y",'Y6'!AF2:AF33,"1") and replace 1 with 2 or 3.

  12. The previous post didn't look good. Sorry!
    A1=Data1;B1=Data2
    A2=123;B2=blank
    A3=123;B3=blank
    A4=123;B4=OMIT
    A5=456;B5=OMIT
    A6=778;B6=blank
    A7=blank;B7=blank
    A8=989;B8=blank
    A9=879;B9=blank

    Thanks again,

  13. A B
    123
    123
    123 OMIT
    456 OMIT
    778

    989
    879
    Count = 4

    How do I get B10 = 4 (do not count column A when there is "omit" on column B)

    Please help and thank very much.

  14. i have two column and i want common values from both column should be highlighted in one column. i doing this manually so it works but not working this formula in code. formula is=COUNTIF(H1:H40,D1:D136). i am using same in code but its not working

  15. if the number is 15 digits (37AAYYCC0866A1Z) its ok less then 15 digits number is not Ok give me the formula

    1. A Rajesh, assuming your first number is in A1, then use LEN(A1)=15. This will give you TRUE or FALSE values.
      If you must have "OK"/"Not OK" then use if(LEN(A1)=15,"OK","Not OK").

  16. Hi Ablebit team.
    I am trying to count the number of cells in a column which contain a 6 digit number that starts with 5, and the third and fourth digits of that number are 60 (for example, 52601, 53607, etc.).
    I have tried =COUNTIF(C:C,"5?60??") and it always calculates the answer as zero.
    I have also troubleshooted with other wildcards ("5*", "5?????"), etc and every time it answers zero.
    The only way I can get a real value is if I set the criteria as a full 6 digit number (for example, "516000")
    Am I using the wild card incorrectly? Please help! Thank you.

    1. Hi EY,
      This is a deep problem with how Excel is storing the numbers you are searching. In using those wildcards, Excel tries to match text values, not number values, as Svetlana alluded to above. There are several fixes, none of them ideal:
      1. Change the cell format from General to Text. This will cause problems if you ever try to enter a formula in those Text formatted cells.
      2. Insert a new column D, with a function to convert the number to text, e.g. cell D1: =Text(C1,"#") and copy that formula for the rest of column D down to the last number you have in column C. If column C is already a formula, wrap your formula inside the Text function, e.g. cell C1: =Text(Sum(A1,B1),"#")
      3. Insert a new column D and do the matching using text functions. e.g. cell D1: =AND(LEN(C1)=6,MID(C1,1,1)="5",MID(C1,3,2)="60"). This checks that length is 6, the first character is 5 and characters 3 to 4 are 60; you can then do a count of cells with a TRUE result, e.g. =COUNTIF(D:D,TRUE)

  17. What i need is,

    i need different article numbers to be counted in a list but if it has 0 on stock than i dont what it to be counted.

    looking forward !

  18. Nicely Explained!
    Q)
    Suppose I have a Columns which contain some text and numbers including some errors also like (#N/A,#VALUE etc). How to count only text and numbers cell not including any error in it.

    Thanks in Advanced. :)

    1. Muhammad:
      I think this formula is what you're looking for:
      =SUM(IF(ISERROR(A65:A76),1))
      I used the range A65:A76 for my test, but you can replace this with whatever range suits you.

  19. I have squardon in cell B.. I have training Status in Cell G. I am trying to make a formula that stats If cell B is (certain squadron) and Cell G is Overdue than put number in Cell K..so if 10 civil engineers are overdue for training.. Cell k should say 10..? of it medical squadron has 7 people over due. it will pick just those medical squadron people out as overdue.

    Goal is to have multiple squadrons on one sheet and with current and overdue as training status and then break it down between squadrons on amount of people current and overdue.

  20. Hi,

    I have Year in Column A (from 2005-2012), names of countries in Column B (like India, Australia, England, South Africa, etc.) and whether they "Win" or "Loss" in Column C. How do I use the Countif function to determine how many times India Won in total?

    1. =COUNTIF(IF((B2:B1000="India")+(C2:C1000="Win")=2;1);1)

      1. ={COUNTIF(IF((B2:B1000="India")+(C2:C1000="Win")=2;1);1)}
        Remember, it is an array formula. Control+Shift+Enter

    2. Vani:
      Not sure about using COUNTIF. You can use COUNTIFS like this:
      =COUNTIFS(A20:A27,"India,C20:C27,"Win")

      1. Thanks a lot. It full fill my requirement.

  21. How can I use COUNTIF to check for duplicated entries in excel?
    For example, if I have this the file below, how can CountIF show where the same STAN duplicates.

    STAN Output
    1234
    2345
    1234
    5678
    890
    5678
    6930

    1. Edozie:
      Where the STAN data is in A2:A8 the formula is:
      =COUNTIF($A$2:$A$8,A2)>1 then copy it down the column.

  22. Hi All,
    =COUNTIFS(G:G, "12345",F:F, "Entry",E:E, "=500")

    This formula counts well for the occurrence of the number 12345 with respect to the matching value in column E and F as mentioned in the formula.

    But to make the formula worked for the next number below 12345, how will I point the next cell in this formula for the column G.

    Basically I need mention the cell (G) instead of number in this formula. Tried many ways.. cudnt win.
    Please help.

  23. How to compare a condition between two table using COUNTIF function?

    I mean suppose I have a column containing 'responsible party' name and another one 'rectification status'. How can I search for a condition between them using COUNTIF function?

  24. I would like to make count of repeats in a column where another cell is blank.
    Example
    Number of times orange repeating where price of orange is $0.5

    Another example

    Number of times persons visited Dubai from Japan
    Name Visited to from which country
    ABC1 Dubai Japan
    ABC2 Paris Japan
    ABC3 Dubai China
    ABC4 London Japan
    ABC5 Dubai Japan

    Here the answer is 2

  25. Hey would anyone know how to use COUNTIF to compare two columns of data - dates, only counting IF the first column date is proir to the second? 30,000 data points so I cannot do it manually

    1. Nick:
      I think this is what you're looking for:
      =COUNTIF(E6,">"&D6)
      Where the dates are in columns E and D beginning in row 6 enter this in column F.

  26. IFEOLUWA AKINNLO W/D ENGINEER
    RAWLINGS U UWUIM BRC OPERATOR
    JIMOH ISHMAEL AD W/D OPERATOR
    AKANDU OKECHUKWU ELE Electrician
    HENRY EYO DAVIS ELE Electrician
    JOHN ETIM W/D OPERATOR
    FRANKLIN IGHOROD ELE HOD
    LASISI OLADIPUPO ELE D.G OP
    ALEX ADEWOLE W/D OPERATOR
    JIMOH RASAQ W/D OPERATOR
    ADELEKE KAZEEM BRC OPERATOR

    I WANT TO COUNT HOW MANY OPERATOR PRESENT DATE WISE. USING COUNTIF FUNCTION.

    1. Dillip:
      If the data is in the range A2:C27 the formula in D2 looks like:
      =COUNTIF(A2:C27,"Operator")

  27. For some reason the COUNTIF formula with dates isn't working on my spreadsheet. Say I have a list of dates in M/DD/YYYY format across multiple rows and columns. I want to use a formula to count how many of those dates fall into a particular week.

    A B C D
    1 4/26/2018 5/08/2018 5/15/2018 5/17/2018
    2 4/26/2018
    3 4/24/2018
    4 4/26/2018 5/15/2018
    5 4/24/2018
    6 4/30/2018 5/16/2018
    7 4/23/2018 4/30/2018 5/17/2018 5/14/2018

    =COUNTIFS(A1:D7,">=&4/20/2018",A1:D7,"<&4/30/2018")
    It should return 7, right? So why is it returning 13? I use the "&" because otherwise the value returns a 0. I'd appreciate the help.

    1. Jon:
      Does your Excel version support COUNTIFS? I use COUNTIFS for this and got the correct number which is 6.
      =COUNTIFS(O23:R29,">=4/20/18",O23:R29,"<4/30/18")
      When I changed the second criteria to be "<=4/30/18" the result is 8.

  28. I'm working on a project and need to use greather or lesser than in the same Countif statement, can anyone help me can't seem to get it right.

  29. Hi,
    I'm trying to count if there was growth in a period and how much time the growth has sustained. So I need to start my count in a values greater than 0 to determine the length of time. My problem is, starting a count on the first negative number in a column determined by time, not counting every positive number in the same time series.

  30. So I was wondering if and how you can set the criteria of COUNTIF so that one row equals another? Meaning I want to count B2:B100 if it equals A2:A100, in other words counting how many times column B equals column A in the same row.

    1. In column C, I would add an if statement.

      if(A2=B2,1,0) then AutoSum (or countif) column C.

      1. Just found a better (quicker) way!!!

        =SUMPRODUCT(0+(A2:A15=B2:B15))

        Thanks, Barry! https://superuser.com/a/846065

  31. I'm having trouble with a formula. I have =IF(COUNTIF(C2:C6,C2)=5,"YES","NO") and it works beautifully, except that I also need the result cell to remain blank if any of the cells C2:C6 are left blank. Help?

  32. Hello
    "If you want to count cells that contain an actual operator as part of the cell's contents, i.e. the characters ">", "<" or "=", then use a wildcard character with the operator in the criteria." It saved my life. Thank you

  33. I'm trying to be able to take a column and count unique values (non-duplicates) and not count blanks. Haven't found the formula yet.

    Ex. We assign ID numbers to each rqst submitted. I keep track on a spreadsheet. Each # may be used for multiple locations so they can appear more than once. I want to know how many unique ID#s I have and not count the blank cells (that are holding for new data to be entered).

  34. Hi Can you assist,

    I am using a countif formula and want to include indirect to count the number of "P" between 2 dates. I have this formula working but cant seem to get to work using indirect as get #value I want to use indirect as I have several similar sheets and just want 1 summary page.

    Working formula not using indirect
    =COUNTIFS(Sample!6:6,">="&B5,Sample!6:6,"="&B5),(INDIRECT("'"&$G$2&"'!"&$C6,"<="&C5),(INDIRECT("'"&$G$2&"'!"&$C8),I$7)))

    G2=Sheetname which one is called sample
    C6=6:6
    C8=8:8
    B5=From date
    B6=To date
    I7=P

    Thanks
    Matt

  35. how do I get countif to count the value in a cell, i.e. if the cell references another cell or is a formula, "=H40" countif will not count the value in the cell, but only what is written in the cell. How do I work around this???

    1. will yes counties does work on some formulas but not the one I am interested in

      =COUNTIFS($H$50:$L$50,"=10")

      will not count the output of

      =TEXTJOIN("",TRUE,IFERROR(MID(H49,ROW(INDIRECT("1:100")),1)+0,""))

      there are values in the cells that countif just does not see and all I get is zero.

      1. found the problem numbers were being stored as text

  36. Hi,

    Need to count the days in Excel between two values in the same column.
    For EX: How to count the value between less than 15 and greater than 30 days

  37. please help... my professor asked as to count the name of anne so we make the formula =countif(A1:A12,"*anne*") but we need to make the string color green when the excel counts the name anne... what formula should we use? please i need answer in this confusing activity

  38. i want to fine a value for a cell based on database

    number value need ?
    5 1 5 what is value
    8 2
    2 3

  39. Helllo - Please can you assist.
    I am currently using the following formula and when there is nothing to display it shows a 0 how do I change the formula to show blank instead of 0

    =COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY())

    many thanks

    1. I would use the following to hide 0 results:

      =if(COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY())=0,"",COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY()))

      I am sure there are better answers than this, but it works for me.

  40. Please help with my formula. I'm trying to get a total number in a cell based on text in a cell range (2 different parameters). My formula is as follows: =IF(COUNTIF(G3:G30, "Complete") < 1,(G3:G30, "Terminated") < 1,0, COUNTIF(G3:G30, "Complete")(G3:G30, "Terminated")) It is not working correctly. Any idea what I'm doing wrong? Thank you.

  41. HELP PLS! I have a list of dates in Coulmn L and then a list of dates in Cloumn M. I want to be able calculate network days between L:L and M:M and if the difference is 3 don’t count. I then want to sum all the 1’s! I would like to do this all in one formula and without a helper column if possible. I have over the 1000 dates in column L and the same in coulmn M. Is this possible?

  42. how can i use wild cards for numbers

  43. I have 2 columns of data. Column A contains a date. Column B contains a temperature. Temperature measurements are made several times each day, so several rows will have the same date. I'm trying to determine how many unique days a specific temperature is reached. So, if a temperature is reached several times during the same day, it should only count as "1" occurrence.

    1. Hi Steve! I have the exact same problem! Did you ever figure out how to do it?

  44. I'm trying to set formula to keep track of wire by feet. B2 does not have a figure but will have one and B2 will change as wire is depleted. I want B2 to subtract entries in cells B3:B27 and show the balance of feet on hand in B2. I've put in formulas for subtracting but I can't put a formula in B2 while keeping the balance of wire in that same cell B2. HELP!

  45. i want count for example how many (+) in an excel function in a cell
    A1=4+5+6+7
    so the count will be 3
    is there a a function for that ?
    or to count how many characters will be calculated
    for the same example it will be 4

  46. PLEASE PLEASE HELP!

    If I want to count or sum a COLUMN, I can do =sum(F:F) for column F

    Let's say cell A1 has F in it,

    How can I reference A1 to do sum (so I can change that cell to do different columns like G, H, I, J).

    I tried like =sum(&A1&:&A1&) but that doesn't help - I was hoping that would calculate as = sum(F:F)

    Thanks!!!!

  47. Hello,
    could you please help to fix my formula? something is not working here
    =COUNTIFS($K$20:$K$734,"????????",$D$20:$D$734,"*.08.*")
    where i have a table for date in text format (.08.), and another column where added some text. basically i need count how many cases in column K from the 8th month

    1. Hello,

      Actually, your formula for is correct, if it doesn't work, most likely there's something wrong in your table.
      If you still require our assistance, please send us your workbook with the data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Don't forget to include the link to this comment into your email.

      We'll look into your task and try to help.

  48. I would like to count non-empty cells, but using the count if formula, it also counts the empty ones because they have a formula that return a value of nothing/blank/empty. How to make excel not to count these formulated empty cells?

  49. Need Help

    Data
    1 2 3 4
    Alpha 1 Alpha 3 ? ? ?
    Beta 1 Beta ? ? ? ?
    Gama 1 Gama ? ? ? ?
    Beta 2
    Gama 3
    Gama 4 Formullae required How "3" Comes In Alpha 1
    Alpha 1
    Beta 2
    Gama 1
    Beta 4
    Gama 4
    Beta 3
    Beta 4
    Gama 1
    Gama 2
    Alpha 1
    Beta 1

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  50. 81150001% is not available at the moment ?

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