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

  1. Please i want to count between a range using = and less than but its not giving me the right answer. This is it: =COUNTIF(H8:Z8,"=12")-COUNTIF(H8:Z8,"<16"), with this formular, its giving me -15.0, whereas if i count manually its 16. but when i use =COUNTIF(H8:Z8,"=12")+COUNTIF(H8:Z8,"<16")(changing the minus sign inbtween the two countifs to plus) i get 19. Please help with this.

  2. hello can you please help me i have a sheet with date delivery to the customer and im trying to use countif formola to have date more than X date but its not working and error all the time what should i do ? thanks

  3. Dear Sir,

    Could You please provide the formula regarding below mentioned data.

    DRIVER Trips 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan
    157 62
    213 63
    223 52
    270 58
    393 48
    400 51
    410 74
    415 39
    433 51
    450 51
    495 31
    The above mentioned details for the month. we need to category like date ways from master sheet.

    An early response much be appreciated.

  4. I'm trying to count the number of times a date between 1-Jan and 31-Jan is entered into a column. The text in the column is formatted as 1/1/2019 which displays as 1-Jan. I've tried the formula:
    =COUNTIF(G4:G304,"*1/*")
    and I've tried
    =COUNTIF(G4:G304,"*Jan*")
    and I keep getting zero as the answer when the answer should be 22.

  5. =sumproduct((H2:H10"")/countifs(H2:H10,H2:H10,G2:G10,Q2,A2:A10,P2))
    This formula works fine only up to 10 cells if increase the range gives #Div/0! Error can some please help in this regard..my data is like this in column A,G,H I have data in column H I have unspecified repeated data need to count this with multiple cretaria

  6. Trying to use the COUNTIF to report the number of instances in the combination of two columns. I want to count when Col P contains "A" and Col L contains "No email". Cannot seem to get this right.

  7. I'm trying to learn more about COUNTIF formula with multiple OR conditions. In that section in the article above, it gives two examples how to accomplish this: +SUM(COUNTIF(INDIRECT({"B2:B8","D2:C8"}),"=0")) and
    =COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0) ) It then states they both return the same result. I understand that. When, however, one references the given photo of the data set, shouldn't the explanation actually read +SUM(COUNTIF(INDIRECT({"C2:C8","E2:E8"}),"=0")) and
    =COUNTIF($C2:$C8,0) + COUNTIF($E2:$E8,0) ) NOTE my change from "B" to "C" in the first range and "C" to "E" in the second range. The formula bar in the data set photo seems to support this.
    If it is actually correct as written in the *explanation, could you please help me understand why? WITH MANY THANKS !

  8. Thank you very much for sharing the information.. very useful

  9. i want to count the number of cells in one row that are less than corresponding cells in another row.

    I tried this formula; =COUNTIF(B12:j12,"<"&B$15:j$15) and it does not count.

    Row 12 is: 5 5 5 6 6 7 4 5 6
    Row 13 is: 5 5 6 6 7 7 5 5 6

    The count should be three.

  10. Hi
    i have shift roaster and the shifts are marked as A1, A2, B3 etc.. so i want to count the number of guys coming in A1 shift, A2 shift like that let me know how can I count this.

    Emp1 A1
    Emp2 A2
    Emp3 B3
    Emp4 B3
    ........

  11. i want to countifs only the cell value with dates in a range of cells with numbers, texts and dates
    for example

    12-12-18
    11-12-18
    ok
    Done
    13-12-18
    2333

    countifs only cell value with dates

  12. I have an array (say I5:Y5) with each cell holding a string (say 1-2-0, 3-0-0, 1-1-1, 0-2-1, etc). I want to count the number of cells in the array where the first number in the string is greater than the second number in the string. I have tried:

    =COUNTIF(I5:Y5,(LEFT(I5:Y5,1)>(MID(I5:Y5,3,1))) with no luck and have tried various forms (such as using quotes around the >, quotes around the formula, etc.), still no luck. It seems there is no COUNTIF formula where I can count when the first number is greater than the second number in the string. Can this be done, either via editing this formula or by VBA code?

    1. Hello, Dennis,

      The formula won't work with COUNTIF. Try using SUMPRODUCT instead:
      =SUMPRODUCT(--(MID(I5:Y5,1,1)>MID(I5:Y5,3,1)))

      Hope this helps!

  13. i want to know how to count zone wise Serial no on Excel file

    Zone count of Serial No
    North
    East
    South
    West
    Total

    Zone Serial No
    North 100972
    North 100973
    West 100974
    East 100975

  14. =COUNTIFS(F2:F255,"FTD - Operations")=COUNTIFS(G2:G255,">="&E258,G2:G255,"="&E258,G2:G255,"<"&EDATE(E258,1)) IT COUNT THE DATE REQUIRED

    I NEED TO COMBINE KINDLY HELP ME OUT
    THANKS IN ADVANCE
    MY ID MAQBUL2005@GMAIL.COM

  15. I have a list of dates, want to count the number of people present in each month.
    Can someone help me out

  16. I am trying to write a formula for a row. I want to exclude blank cells as well as cells with dates in them. Every formula I have tried, I end up with a date for an count.
    Please help, and thank you!

  17. Phill:
    Have you tried putting the < in double quotes? Like this:
    =COUNTIF(E2:E6,"<"TODAY())

  18. I am using the COUNTIF function to look at a number of date cells and count the number that are overdue. If i type the formula =COUNTIF(E2:E6,"<1/12/18") it returns the number 3 which is correct. I want the formula to use todays date automatically but substituting 1/12/18 with TODAY() returns zero. Have also tried substituting 1/12/18 with a cell (B12) but again 0 returned. Any ideas?

    1. Add the ampersand, freund. See below

      =COUNTIF(E2:E6,"<"&TODAY())

  19. I WANT TO KNOW THE FARMULA FOR CALCULATE THAT
    IF 3971 * 1.75 % = 69.4925 THAN WE REQUIRED HERE 70

  20. I have many cells where I used " countif " formula. I need to sum all these cells .When I use the Sum fucntion or I add the cell using "+" they result is always zero.

    Whoever, When I select the cells , it gives me the right total on the bottom of the excel window.

    How can I solve this?

    Thanks,

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

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

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

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

  25. Thank you for sharing this information

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

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

  28. Thank you so much for this!

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

  30. Thank you for sharing this information

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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