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

  1. Thank you for being very helpful, I am looking for an answer to my question, Is there a formula to this situation?
    Name Amt
    AA 40
    DD 45
    AA 48
    GG
    SS 54
    AA 67
    GG 85
    AA
    AA 12
    KK
    AA 6
    LL 15
    AA
    QQ 4
    Count all # of "AA" if there is a value in column "Amount"

    1. Hello Harry,
      If your task is to count all the AA entrances that have any amount, please use the formula below:
      =SUMPRODUCT(($A$2:$A$15="AA")*($B$2:$B$15<>""))

      If this is not exactly what you need and you'd like to sum these amounts, here is the formula for you:
      =SUMPRODUCT(($A$2:$A$15="AA")*($B$2:$B$15))

  2. Hi Team
    I have a spreadsheet which includes data on customer enquiries including customer ID, enquiry type, date, referral source etc. To count the enquiries by business area (made up of different referral sources) in a given month, I am using the following formula:
    =SUMPRODUCT(COUNTIFS('MYREPORT'!$H:$H,"ENQUIRY TYPE A",'MY REPORT'!$B:$B,CATEGORYLIST!$H$2:$H$27,Enquiry_Date,">="&P2,Enquiry_Date,"<"&EDATE(P2,1)))
    P2 in the above is the month I am totalling enquiries for. The reason for the 'CATEGORY LIST' part is so that I can group various different referral sources (people) into one reporting point e.g. 'TEAM A'.

    My problem:
    I need to be able to count a different enquiry type, 'ENQUIRY TYPE B' in the same way, but want to exclude any occurrences where the same client has already had an 'ENQUIRY TYPE A'. I am finding this difficult especially as my initial formula above does not refer to the unique client ID field and in order to exclude clients who already have had an 'ENQUIRY TYPE A', I am presuming it needs to.

    Many thanks for any help you can give

  3. Hello, I have a dynamic data set that is being changed daily (new rows being added to the top) and I want to have a unique ID created for each distinct set on "today's" date.
    so for example the data-set may look like the following
    nov 2, 2019 5704
    nov 2, 2019 5704
    nov 2, 2019 5705
    nov 2, 2019 5705
    nov 2, 2019 5706
    nov 1, 2019 3416
    nov 1, 2019 3416
    nov 1, 2019 5704
    oct 31, 2019 5705
    oct 31, 2019 5705

    So on the date of Nov 2, 2019 I have 3 distinct rows. I will be adding new rows to the top every day by using a macro so the data range should extend down in order to continue capturing all the data. My preferred range would be something like A:2 to A:3000

    as of right now I have a formula that tells me how many distinct sets I have in the entire data-set but I dont want that I want it to be able to look at the entire data set and only tell me how many distinct sets there are on today's date. I have tried a couple of things and I cant seem to figure it out. Here is the formula I currently have
    =SUM(IF("range of column 1""",1/COUNTIFS("range of column 1","range of column 1","range of column 2","range of column 2"), 0))

    Extra Note:
    If I were to apply this formula to the data set i have above it would give me a result of "6"

  4. Hello. I think I'm on the right track and the COUNTIF function is what I need.
    Say I have a table in column C with some blank cells, and I want to add an adjacent column D but omit the values in column D which are a next to a blank cell in column C. Would I use the COUNTIF or SUMIF function? the cells in column D need to be summed up. Also, can you help me out with the syntax?

    Thanks

    1. Yes you can, but you'd want to use COUNTIFS as this allows for multiple sets of criteria.
      something along the lines of:
      if your using numbers then
      =COUNTIFS(C1:C10,">0",D1:D10,">0")
      if your using text
      =COUNTIFS(C1:C10,">""",D1:D10,">""")

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  33. Thank you very much, this is helpful.

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

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

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

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

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

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

  40. Thank you so much!!

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

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

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

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

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

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

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

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

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

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

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