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 5. Total comments: 1080

  1. Hi I wanna count how many awaiting report and good run column A with a criteria of today-2days. I'm having trouble building a formula please. Thank you

  2. Dear, Please solve my Problem. i try to develop formula of COUNT, but not count Duplicate value.

    Inv. No.
    18
    18
    20
    21
    627
    628
    628
    630
    631
    632
    632

  3. You have to sort out your days of the year against time. In column A sort the Dates and in column B sort the time.
    Column C
    ={INDEX($A$2:$A$1000,MATCH(0,COUNTIF($C$2:C2,$A$2:$A$1000),0))}
    Column D
    {INDEX($B$2:$B$1000,MATCH(0,COUNTIF($D$2:D2,$B$2:$B$1000),0))}

    Column to get the above sorted date and time count
    ={COUNTIF(IF(($C$2:$C$1000=$C2)+($D$2:$D$1000=$D2)=2,1),1)

    =COUNTIF(IF((B2:B1000=Date(2019,07,02)+(C2:C1000="15:30")=2;1);1)

  4. I have the following problem. I want to be able to track the number of leads I got on my website during the year, or perhaps even over several years of time and plot this in a graph. I have a column with the date I got the lead and a column with the time I got it.

    I can use count if to get a column with the number of times a certain data appears. I would like to do this for every date in the year. For example 20 leads on January the first, 25 on January the second, etc. etc.

    How do I do this without having to input every single date of the year?

  5. 1st column salesman , 2nd product name, 3rd quantity of sale,,,salesman name & products r repeated,,,so it is possible that with countif formula salesman sales same product many times & how much sales? Results should be sum not count 5tine,,,result example - 1st time 10, 2nd time 14, 3rd time 7= result should be 31 not 3

  6. Please help,
    In my Data, 1st column Date (reflecting 120 days)2nd column repeated names against various dates of 1st column (like A, B,C,D etc). how to count number of A,B,C,D between two dates? please help

  7. I have following text values in cells:
    9
    <8
    <7
    How to do countif to find only cells containing <7 expression?
    Any ideas?

  8. Hello,
    I need a function which one would count in range (A1:A999) words YES, but starting from last NO, for example YES YES NO YES YES YES, answer would be 3. Anyone could help please?
    Thank you in advance :)

  9. hi there
    i got a question , how can i make a formula that doesnt count 0 but show less then 0, for example in my file i got a row that show my active items in store and in other row it shows the number of negative margins from that item
    i did this formula for it
    =COUNTIFS(MKT!F:F,"AC",MKT!Q:Q,"<0")
    but it count the "0" number for "AC", i just want lower then 0 not the zero number

    1. you might have small decimal number e.g. 0.00001

  10. Monthly Stipend Sheet of Research Scholars

    SrNo. Scholar Name Gender Stipend
    1 Mr. Muhammad Male 69,000
    2 Miss Farzana Female 45,000
    3 Miss Zanub Female 55,000
    4 Mr. Arslan Male 59,000
    5 Mr. Shafeeq Male 59,000
    6 Mrs. Faiza Female 62,000
    7 Mrs.Shagufta Female 55,000
    8 Mr. Ali Ahmad Male 68,000
    9 Mr. Kashif Male 45,000
    10 Miss Saima Female 69,000
    11 Mr. Salman Male 62,000
    12 Mr. Asim Male 55,000

    2. In cells “H8” and “H9”, enter a formula to calculate the total stipend for males and females.
    3. In cells “I8” and “I9” calculate the average stipends for males and females using the values calculated in question 2.
    4. In cells “J8” and “J9”, calculate the number of males and females having stipend more than 50,000.
    Please please help me with this.

  11. My Formula
    =COUNTIFS('Daily Activity Register'!$M:$M,"Vidhu.Khosla",'Daily Activity Register'!$B:$B,"Walk-in",'Daily Activity Register'!$AH:$AH,"N",'Daily Activity Register'!C:C,J18) isfailing When last condition i am trying range of dates.
    Daily Activity Register'!C:C is dates

    1. When i use the condition ">="J18 it says Invalid

  12. Thanks for these enlighten tips.
    I just have a comment with respect to the unique/duplicate count.
    (from your example 3)
    =SUMPRODUCT((COUNTIF(A2:I2,A2:I2)=1)*(A2:I2"")
    Sometimes, we need to count unique elements in a row or column BUT that count should include one instance of the duplicated element as well (as you surely note, this a reasonable conceptualization of the unique definition). Is there a simple formula to include those single instances?

    Thanks

  13. Hi there,
    I am trying to create a database and am struggling to count the number of clients between certain ages and certain admission dates. I am using defined names and my formula looks like this:
    =COUNTIFS(Dyn_Gender,"F",Dyn_Age,">64",Dyn_Age,"=01/04/2019",Dyn_Admission_Date,"<=30/04/2019")

    However I am not getting any results when there should be at least 1, any advice or workarounds? I am unsure which part of the formula is incorrect...

    1. Sorry typo in formula above, refer to this:
      =COUNTIFS(Dyn_Gender,"F",Dyn_Age,">64",Dyn_Age,"=01/04/2019",Dyn_Admission_Date,"<=30/04/2019")

  14. Hi, I have five columns. I have created created a drop down menu list having three (empty column, 0 & 1). I want to use such formula that count all number of columns (even empty columns) whenever a value is entered in one or more columns. Explaining further, if a value is entered in first column and remaining columns are empty, the sum should be 5 (i.e 1 column that contain a value, and 4 remaining empty column). Help me Please!

    The main question is that it should count only when there is a value (0,1) in any of column and it must count all the column either there is a value in column or it is empty.

  15. Hi
    I am trying to set a formula that if a total is less than a set amount then that amount is charged at 1 rate but if it is greater than x then times by another amount. i.e. if it is ,3.7 then *30 but if >3.7 *20
    can anyone help ?
    rgds
    James

  16. Gents,
    how can i create count formula where the first column is NOT blank AND the second column IS Blank.
    Please Its urgent
    Best Regards
    Toufik

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

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

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

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

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

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

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

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

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

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

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

  28. =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?)

  29. Thank you very much, this is helpful.

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

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

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

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

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

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

  36. Thank you so much!!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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