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

  1. I have a column with dates and I am trying to count how many have a date in them. I have used =countif(c2:c2885,"*") and it works lovely on everything but dates. Any suggestions would be fantastic.

    1. Hi did you receive an answer for this question?

      I have a column with a long list of dates and another column with individual dates. I wanna count how many times the specific date in the second row comes up in the first row.

      EG: Countif(range,a1)
      Range: long list with multiple dates
      a1: cell with the specif date that we are looking for

  2. I want to calculate the particular product and their quantity sum
    Like
    Cell a(Sun). b(time). c(quantity).
    Product. X 0:05. 10
    Y. 0:10. 15
    X. 0:10. 05
    I have already applied one formula in this to calculate how much x and y is but now i want to add the quantity too
    The applied formula is. =Countif(a2:a34,"x",b2:b34,">="&e9,b2:b34,"<"&f9)
    In this fromula i have to add the c(cell) same quantity to add the sam in this formula

  3. Hello There, I am trying to use a COUNTIFI have 2 different values in different columns resulting in 2 ranges.
    I want to count if the numbers from 1 range is less than or equal to the other range but I seem to be striking out. can anyone help?

  4. Thanks for the great blog you've set up at ablebits.com. Your enthusiasm is absolutely inspiring. Thanks again!

  5. Hi, I can't seem to figure out the formula to do the following: I have 2 columns. Column A is either blank or a number from 1-9. Column B is blank, 1, or 0. I want calculate the number of 1s in Column B IF column A also equals 1. (I'll end up doing the same thing for 2-9).

  6. With the "countif"function, can it count only whole integers? On my sheet, I want the abbreviation "RALR AM" to be counted as just a half, .5, so I can show I have someone just working half a day and not screw up my employee count for the whole day.

  7. I have a range of numbers. I want to do a count of anything in that range that is another cell of dates. Can you please help me? Thanks.

    EX range list of dates
    5/5/2019
    5/7/2019
    5/9/2019

    then single cell of dates
    5/5/2019 5/12/2019 5/17/2019

  8. track contact attempts per person with the dates and times with a space in between that date and the time in each cell. I want a cell to total all my contact attempts for today. I tried countif with my criteria as today(), but that's not working since I have times in the cells also. What should I put for the criteria so countif will total up all the cells containing todays date along with any time? Thank you.

  9. I've a data set containing number of reported illnesses (1 for the illness, 0 for no illness). I want to count the number of illnesses per sex (male and female) without repetition, meaning once a male household has malaria, all other male households have it. How do I do this?

  10. I want to count blank cells in a range, and exclude any cells from the count that have spaces in them. (Where the cell appears to be blank, but actually contains a space)

  11. Thank you... this was driving me crazy!!! THANK YOU SOO MUCH

  12. =COUNTIF(range,""&"")
    this counts also cells containing FORMULAS!
    For instance if I have a formula that produces either a number or empty cell (based on some calculation) the above countif formula will count even the empty ones.
    I had to use the =COUNTIF(range,""&"*") formula as I had only number or EMPTY cells. Dont's know however if also date and other data types would be outcome of calculating formula, how this would end?

    1. as I was afraid, this is not counting cells containing strings, only numbers and may be dates?)

  13. Thank you very much, this is helpful.

  14. Hello I have a spreadsheet that contains list of applicants, datetime applied(datetime) and on the other sheet date(date), total applicants. Im trying to count all the applicants within that date by counting the applicants with specific date comparing on the date(date) on the second sheet. my problem is how to convert range into int so that I con compare date and datetime. COUNTIF(INT('Applicants'!$B$2:$B$13), INT(A2)). Can anyone help me with this problem

  15. I HAVE A PROBLEM I HAVE A SPREADSHEET THAT CONTAIN PEOPLE'S INFORMATION FROM THAT I NEED TO KNOW THE NUMBER OF THEM ACCORDING TO RACE,GENDER,post levels AND AGE.
    Race is on column D
    Gender is on E
    Age is on F
    Post levels on G
    Age on K
    THIS IS A BIG SPREADSHEET I CANT COUNT MANUALLY. PLEASE HELP

  16. I'm trying to count the number of times a unique word is used in a single cell, i.e. =IFCOUNT(A1:A1,"thus")
    Looking for over use of a word. Cell A1 contains a sentence. All my results come out zero (0) even though I can plainly see that the word is in the sentence 3 times (all lowercase).

  17. I would be grateful if someone can help me. I have a list of board directors and the companies they are directors in.
    My Excel data list is quite large, and here is an example of how it look like.
    Name of director Companies
    A Company 1 Company 4
    B Company 1 Company 2 Company 3
    C Company 4

    I need to convert it to a matrix table like this
    Company 1 Company 2 Company 3 Company 4
    A 1 0 0 1
    B 1 1 1 0
    C 0 0 0 1

    Any ideas, how can I Do this.
    Thanks

  18. I’m trying to figure out how to replace the string for the condition to a variable. As in:

    COUNTIF($A$1:$A100, (text in cell to the left))

    How would I do this?

  19. I was looking for a COUNTIF formula that would count non-blank cells that include both text and numbers. Your tip saved me a ton of time! Thank you.

  20. Thank you so much!!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  45. Thank you for sharing this information

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

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

  48. Thank you so much for this!

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

  50. Thank you for sharing this information

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