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

  1. Hi Alex

    Is basically counting the "blanks" at "L" if the the time at "k" more than required(08:30, 14:30 and 20:30)

    kind regards

  2. Thank Alex

    Please assist I am trying to identify reason for late start, and where there is late start but no reason booked, hence I want to count the blanks if the start up was greater than require of 08:30, 14:30 and 20:30 in different shifts.

    Time should be greater than 08:30 and less than 13:00, and greater than 14:30 and less than 18:00 and also greater than 20:30 and less than 23:00

    Column B
    shift
    Morning/Afternoon/Night

    Column k
    Time

    Column L
    reasons for late start and Blanks that I want to count

    1. Hi!
      Add another condition to the COUNTIFS formula.

      =COUNTIFS(B2:B5,">"&TIME(8,30,0),B2:B5,"<"&TIME(13,0,0),C2:C5,"")

  3. May you please assist me.

    I have blank rows with data and without data, which were suppose to be having data.

    I want to count the blank rows if the time is greater than e.g 08:30

    * e.g: countblank C5, if B5 Is greater than 08:30

  4. Hello,
    I encountered this problem with countif function: if in one cell I have the value "5" and in another one "05" it sees them as duplicates. Is there any way to write the function so it doesn't see them as duplicates?
    Thank you

    1. Hello!
      You have not written what condition you use in the COUNTIF function. Therefore, I cannot give you an exact answer.
      I would advise you to check that 5 and 05 are written as text. I assume that 5 is written as a number.
      Describe in detail what problem you have, and I will try to help you.

  5. i want to count only in my data if country =" USA" in one column and state = " Califrornia" in other column.

  6. I am trying to use countif based on certain criteria.

    I have 20 provider names. Each name may be assigned 100 patients. I need to count how many orders that physician entered per patient. Sometimes there are no orders at all. The orders are time stamped by date and time. I need to count the date/time for each specific provider. The date range anywhere between 1/1/2021 00:01 to 3/31/2021 23:59. I tried to just use count A but I wasn't sure how to to use that with Countif

    I am thinking
    =countif(range=name, range,_____)

    I not sure what to specify for my 2nd range.

    Thanks!

  7. Hello,

    I want to use a formula in excel to return the number of "Yes" results in a range, with the result of each inquiry returning either zero (there are zero yes results), or 1 if there is 1 or more of entries of "Yes" in the range.

    Is that possible? I assume use Countif with some additional formula?

    1. Hi,
      If I understand your task correctly, the following formula should work for you:

      =COUNTIF(A1:D50,"Yes")

      If this is not what you wanted, please describe the problem in more detail.

  8. Hi,

    How to COUNTIF specific model (eg. CHEVY) which had expired last 2 weeks (let's say the date today is 01-Mar-21). Moreover, the result will be on another sheet. tnx.

    MODEL Registration# Reg. Date Expiry Date

    CHEVY 313001 03-Jan-20 03-Feb-21
    TAHOE 314001 10-Jan-20 10-Jan-22
    CAMRY 315001 17-Jan-20 17-Jan-22
    CHEVY 313002
    LIBERTY 316001 01-Feb-20 01-Feb-21
    TAHOE 314002 08-Feb-20 08-Feb-21
    CHEVY 313004 29-Feb-20 29-Feb-21
    TAHOE 314003 15-Feb-20 15-Feb-21
    CHEVY 313003
    CHEVY 313005 29-Feb-20 28-Feb-21
    TAHOE 314004 15-Feb-20 15-Feb-21
    CHEVY 313006 29-Feb-20 25-Feb-21

    1. Hi,
      The formula below will do the trick for you:

      =COUNTIFS(A1:A12,"CHEVY",D1:D12,">"&TODAY()-14,D1:D12,"<"&TODAY())

      You can learn more about COUNTIFS in Excel in this article on our blog.

  9. I want to count number of cells in Google Sheet only when another cell contains some text.

    Customer Name
    Total Time
    Work Start Work End 00:00

    Team Used
    Van 1 Van 2 Team Size
    Sr. No. Name Site Name Name 7
    1 Rahul Patil Weekly Off Vikas Wakude
    2 Pradip Vetal Weekly Off Harish Wakude Van1 4
    3 Vijay Patil Weekly Off Suresh Salunkhe Van2 1
    4 Vikas Wakude Weekly Off

    For e.g. I want to count " Name " only when " Customer Name " contains some text.

    Please help.

    Thanks.

  10. Hi There
    I have a column containing over 1,000 entries of about 200 company names. I want to count the number of times each company name occurs. How do I do this?
    I'd like to save the result in a new sheet.

    Any idea? Thanks for your help.
    Anna O.

    1. Hello!
      You can get a list of unique values using the UNIQUE function. You can count how many times each name occurs using the COUNTIF function.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  11. How to packets counts row numbers multiple other row formula

  12. Can Countif be used with a function in the criteria? My column O has dates. I want to count how many dates are in 2020. I tried something like =COUNTIF(YEAR(O8:O609),"=2020") and =COUNTIF(O8:O609,"YEAR()=2020")

    1. Hello!
      Please try the following formula:

      =SUM(--(YEAR(O8:O609)=2020))

      COUNTIF does not work with arrays, so cannot be used YEAR(O8:O609)
      I hope I answered your question. If something is still unclear, please feel free to ask.

  13. I have built a complicated spreadsheet in Google Sheets containing several SUMIF uses. Is there an easy way to add column next to the resulting column giving the number of cells counted in each resulting cell in this resulting column? Here is an example of a cell which is a part of a column of formuli. I want to place another column the the right of this column showing the number of cells which were counted to make the results in the cell to its left.

    =SUMIF(D$10:D$110, AA23, C$10:C$110)

    Thank you for your help.

    1. Hello!
      If I understand you correctly, you can use the COUNTIF function with the same arguments as in SUMIF.

      =COUNTIF(D$10:D$110, AA23, C$10:C$110)

      I hope my advice will help you solve your task.

  14. Hi There,

    I have a column say "A" filled with data. I need a combination of formulas (IF & COUNTIF) on column B to validate firstly that if the cell A is blank, the result is blank. Else, if there is a value, it validates all of column A for duplicates and highlights for "Duplicates", else the result is "Unique". Can you please help?

    1. Hello!
      Please use the following formula/the formula below to solve your task:

      =IF(A2="","",IF(COUNTIF($A$2:$A$10,B2)>1,"Duplicate","Unique"))

      I hope this will help

  15. Below formula is working for I,J,K and L. Except if i put range then the formula breaks.

    Working:
    =SUM((TRIM(I153)"")*(TRIM(K153)"")*(TRIM(L153)"")*(TRIM(J153)""))

    Not Working:

    =SUM((TRIM(I2:I153)"")*(TRIM(K2:K153)"")*(TRIM(L2:L153)"")*(TRIM(J2:J153)""))

  16. I have a formula which seems to be working ok. My formula is to check the columns I,J,K,L cannot be a blank. I am using below. I column is text, K column is text, L column is text and J column is a list.

    =COUNTIFS(I2:I153,"",K2:K153,"",L2:L153,"")+COUNTIF(J2:J153,"0,1,N/A")

    if the criteria is matched then it is counted as a verified row.

    Users are giving a space I OR K OR L which seems to be counting as non-blank and making it as a verified row with no data that makes sense.

    Is there a way to not to count a space as valid entry or just no count if only space is provided in the column.

    Many thanks in advance

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM((TRIM(I2:I153)="")*(TRIM(K2:K153)="")*(TRIM(L2:L153)=""))+COUNTIF(J2:J153,"0,1,N/A")

      I'm not sure if COUNTIF(J2:J153,"0,1,N/A") is written correctly. But I do not have your data and I cannot check the condition "0,1,N/A". I think it should be 3 conditions.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Thank you Alexander for your reply.

        I need more help. You may have understood other way around. Sorry about not to be so clear.

        For example there columns I,J,K,L

        Criteria 1 is: I, J, K and L cannot be empty. Must have at least one character, not just spaces allowed.

        Your formula worked for 3 columns if i made a change like below.

        =SUM((TRIM(I153)"")*(TRIM(K153)"")*(TRIM(L153)""))

        However range is not working. Getting #value error.

        The j column must have a value 0 or 1 or N/A. User will be provided an excel sheet with to select from a list 0,1,N/A.

        Formula in text:

        Columns I2 to I153 must have at least one character (no space) and K2 to K153 (event better if it is a date) must have at least one character and L2 to L153 (no space) must have at least one character and J2 to J153 must select a value 0,1,N/A.

        Hope I am clear on my question.

        Thank you again for your time helping me in this.

        1. Hi,
          The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
          If data in column J is selected from a list and cannot be entered manually, then only non-blank cells can be counted.
          The formula counts the number of rows that have at least one blank cell.

          =SUM(--(((TRIM(I2:I153)="")+(TRIM(K2:K153)="")+(TRIM(L2:L153)="")+(TRIM(J2:J153)=""))>0))

          I hope it’ll be helpful.

    2. actually the formula is this. above question have incorrect formula.

      =COUNTIFS(I2:I153,"",K2:K153,"",L2:L153,"")+COUNTIF(J2:J153,"0,1,N/A")

  17. I have COUNTIFS(H34:AI34,"OFF" in attendance schedule so with the same range i dont want to count "AL" please give me formula where i can put multiple condition.

    For Example
    Range is H34:AI34
    Criteria is OFF and AL which mean i dont want to count OFF and AL

  18. Hi
    I have a list where I need to count the number of records which meet a 2 criteria.
    Application name and Critical or Non Critical

    The application name is contained in a string of text the Critical/Non Critical is in a separate cell.
    Column A, the status Critical or Non Critical is in Column B

    Column A contains one of the following

    Security Global
    Security Global AS400
    Security Global Link
    Security Global Unix / Linux
    Security Global Windows
    Security Global z/OS

    Column B is either
    Critical
    Non Critical

    I have a formula to count the entries for "AS400", "Link", "Windows" etc
    =COUNTIFS($A$2:$A$14,"*AS400*",$B$2:$B$14,"Critical")

    but if I search for Global it brings back everything.
    =COUNTIFS($A$2:$A$14,"*Global*",$B$2:$B$14,"Non Critical")

    Is there an easy way to get it to count the cells which contain just "Global" and not include the "Global Windows" etc

    Many thanks in advance

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =COUNTIFS($A$2:$A$14,"*Global*",$B$2:$B$14,"Non Critical",$A$2:$A$14,"<>"&"*Global Windows*")

      Hope this is what you need.

  19. Hello,
    I have a report that I am trying to figure out a formula for. I am using a countifs formula that has several ranges and criteria. The report has a 1 in every cell of column A, employee name in column B, date of first day in quarantine in column C, date of last day in quarantine in column D, either a 0 or 1 in column E (0=quarantine and 1=positive test and quarantine) and the final few columns are irrelevant to the formula. The information I am looking for is: 1) total positive test/quarantines, 2) total overall quarantine, 3) total 14 day quarantine based on dates in column C and D, 4) total 10 day quarantine based on dates in column C and D. The formula I am using for the each is as follows:
    1) =countif(E2:E800, "1") - correct total
    2) =countifs(A2:A800, "1", C2:C800, ">="&TODAY()-"14", D2:D800, "="&TODAY()-"14", D2:D800, "="&TODAY()-"10", D2:D800, "<="&TODAY()+"10")

    The current total number of employees in quarantine is 7, but the formula is giving 14 which is the total number of employees that are in quarantine based on the date in column C only rather than counting if the dates meet the criteria for both columns C and D. How do I get the formula to consider the second range and criteria when counting? I thought countif functions recognized "and" rather than "or" in the format I am using.

    1. Hello!
      I cannot validate the formula without your data, but I recommend trying the formula

      =countifs(A2:A800, 1, C2:C800, ">="&(TODAY()-14), D2:D800, “=”&(TODAY()-14), D2:D800, “=”&(TODAY()-10), D2:D800, “<="&(TODAY()+10))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  20. Dear All,

    I want to calculate no. years month and days to calculate experience tenure and then calculate that experience with 4:1 ratio please suggest formula anyone.

  21. Hi,

    I have a problem for the last criteria. the criteria should be the value of AO is less than AJ. below is my code.

    Sheets("Visual Data (materials)").Cells(33, i) = Application.WorksheetFunction.SumIfs(Sheets("Raw Data (Materials)").Range("AS:AS"), Sheets("Raw Data (Materials)").Range("C:C"), "GEN", Sheets("Raw Data (Materials)").Range("AK:AK"), ">=1/1/" & Sheets("Visual Data (materials)").Cells(32, i), Sheets("Raw Data (Materials)").Range("AK:AK"), "<=12/31/" & Sheets("Visual Data (materials)").Cells(32, i), Sheets("Raw Data (Materials)").Range("AO:AO"), Sheets("Raw Data (Materials)").Range("AO:AO") & "<" & Sheets("Raw Data (Materials)").Range("AJ:AJ"))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

      1. my question is actually, how can I include a criteria for countifs where values in range A:A is less than range B:B

        1. Hi,
          The formula will work. If these are ranges for criteria, then only data for which the conditions are met in two ranges at the same time will be counted.

  22. My question is simple but I don't know what formula to use to make it happen. I'm a quilter. There are a number of squares I get in a package of fabric squares to sew together to make a quilt. I would like a formula that I can put in the number of squares I have in a certain design and have it tell me how many different combinations of rows and columns I could have. Then show me in a diagram.

    This kind of formula is beyond my skill level. So if anyone would like to figure this out for me, I would definitely appreciate it!! Thanks!!

  23. Hi!
    How do I calculate a count of cells, omitting only the duplicates. that is, if there are 10 entries, and 2 of them appear twice, it should return an answer of 8 (not 6 or 4)?
    thank you!

    1. Hello!
      You can learn more about counting unique values in Excel in this article on our blog.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  24. How do I insert a VLOOKUP as the criteria
    I tried but it keeps failing

    For example :-
    =COUNTIF($G5:DR5,"<VLOOKUP(A5,Codes!$A:$E,4,0)")

    1. Hello!
      It is not possible to insert VLOOKUP as a less than criterion in the COUNTIFS function.
      I recommend using something like this formula

      =SUM(--($G5:$DR5<VLOOKUP($A$5,Codes!$A:$E,4,FALSE)))

      I hope it’ll be helpful.

      1. Thank you :)
        But i did figure out to insert a VLOOKUP as a criteria and it worked

        =COUNTIF($G5:DR5,"<"&VLOOKUP(A5,Codes!$A:$E,4,0))

  25. The formula that I'm using is =COUNT(E143:E419)+COUNT(G143:G419) for the entry's and the =COUNTIFS(F143:F419;">=8:00")+COUNT(H143:H419) and in the result cell is =MAX(0;N1-O1) but it limits the leaving to 8:00 am, do you have a solution for this? Sorry for the bad english

    1. Hello!
      It is difficult to give advice without seeing your data. But perhaps the instruction will be useful to you - COUNTIF formulas for dates.
      Also, I do not know in what format your time is written. But you need to use the TIME function. Instead of ”>=8:00″, use >TIME(8,0,0). I suggest you read this article.

      1. Thank you, I will take a look

  26. Hi

    I need to count the number is cars that are present inside a parking lot. The trick is that sometimes some cars enter the day before and the record is for a single day. I need to count only the entry's but not when it leaves, but I still need to register the hour he leaves. So one camp as the license plate and then the entry hour and the leaving hour, I need to count the leaving hour but only if he has a entry ou else ignore or return a 0 but still count all the others cars inside!

  27. How can one count the number of cells in column A in which the values are greater than the corresponding values in column B? What Excel command should I use? I tried sumproduct but it isn't applicable as it adds up the actual values whereas I only want to know how many cells in A have values greater than B. Thanks.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(--(A1:A20>B1:B20))

      Hope this is what you need.

  28. hi , I really need help , is there a way that I can count text that is in a range but not part of my "List" for example i had people fill a survey one of the qeustions had multiple checkboxes , the one checkbox was open awnser so i want to count the awnsers that are not in the pre specified checkboxes the [] other:_____ text I hope that this makes some sense to someone

    1. Hello!
      Without seeing your data, I cannot write a formula for you. But please reread the article above, it covers your case completely.

  29. I am doing a COUNTIF formula =COUNTIF($C$5:$C$19,"ben*"), but I need to copy the formula to three other cells. When I copy them, the exact same formula comes up for each line, but I need the "ben*" to change each time to a different name (cap, cat, hun). How do I get my formula to recognize that it needs to change without doing it manually?

    1. Hello!
      Instead of “ben *”, write a reference to the cell with this value in the formula. Like this:

      =COUNTIF($C$5:$C$19,D1)

      When you copy a formula, the link will change to a different cell.

  30. How do I count those records where Column X is not blank AND Column Y is blank? For example, Column X is "Drafts Uploaded" and Column Y is "Shipped." I need to count those record for which drafts were uploaded (Column X is not blank) but have NOT been shipped (Column Y is blank).

  31. what a formula be to count a range of blank cells if filtered.

    Ex. have a list, only want to count blank cells. If i only want the blank cells when i filter NY, how can i get it to only count the blank cells when i filter

  32. I have a lengthy list of names with dollars tied to purchases to each of those names. I am attempting to add up the totals for each of the individuals. Some have 3 lines, some have 1 and others have 5. How would I go about getting this to work?

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  33. How do you use countif when the cells you want to count have a return value in the Dated format example below.
    4 Yrs, 11 Mths & 8 Days
    The above shows length of service the formula used is:
    =IFERROR(IF(E6="","",DATEDIF(E6,$C$2,"y") & " Yrs, " & DATEDIF(E6,$C$2,"ym") & " Mths & " & DATEDIF(E6,$C$2,"md") & " Days "),"")

    Now I want to count how many colleagues have a length of service of more that 3 yrs. Is that possible?

    Thanks

    Phil

    1. Hello!
      You can count the number of dates for which the difference is more than 36 months:

      =SUM(--(DATEDIF($E$1:$E$10,$C$2,"m")>36))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  34. I want to count alternate column value. for example i have column A-B-C-D but i need only to count column A & C Value.Please help me to know how i can do by formula.

    1. Hello!
      You did not say by what criteria you will count. Therefore, I cannot give an exact recommendation. However, you can add the two COUNTIF functions. In the first you count by column A, in the second you count by column C. Or you can use the COUNTIFS function. Read more here.

  35. Help me, how to calculate duplicate value for below range? i notice when i use this coding, repeated values such as 93 twice, not counted.
    =COUNTIFS(Z5:Z14,">=90",Z5:Z14,"<=94")

    Thanks in advance

  36. I want to count the no of cell with a particular value in continuation and break the counting is that value changes in a cell. which formula I have to use ?
    SEGMENT NAME 20Jul 21Jul 22Jul 23Jul 24Jul 25Jul 26Jul 27Jul 28Jul 29Jul Desired count reslut (Count <1)
    GOMADBAMANN3 100.00 96.61 76.37 19.06 9.25 0.00 90.24 0.00 0.72 0.00 2
    GOMADBAMANN1 100.00 96.07 76.37 20.99 39.14 86.97 99.94 0.00 76.60 0.00 1
    GOMADBAMANN2 100.00 96.76 76.37 15.67 0.04 52.55 0.00 82.61 78.17 0.00 1
    GOKILKILLIS3 100.00 99.97 100.00 100.00 100.00 97.25 0.00 73.75 33.04 0.00 1
    GOKILKILLIS4 100.00 99.97 100.00 100.00 100.00 97.22 0.00 75.06 33.03 0.00 1
    GOPARMADHAI1 0.00 36.65 19.06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7
    GOPARMADHAI2 0.00 36.65 19.06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7
    GOPARMADHAI3 0.00 36.65 19.06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7
    GOAMRAMRMAS3 100.00 100.00 100.00 100.00 100.00 0.00 0.00 100.00 34.87 0.00 1
    GOJADJADAUD1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10
    GOJADJADAUD2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10
    GOJADJADAUD3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10
    GOHINHINDXX2 99.65 99.24 99.18 99.53 5.48 56.80 99.02 98.91 41.28 0.00 1
    GOBELBELA1X1 0.31 0.00 0.00 0.91 0.58 0.11 0.80 2.67 0.00 0.00 2
    GOFIRSADABA1 99.74 99.66 99.58 99.85 99.74 0.00 0.00 100.00 100.00 0.00 1
    GOFIRSADABA2 99.74 99.65 99.58 99.92 99.74 0.00 0.00 100.00 100.00 0.00 1

  37. 0100000037 288.00 288.00 PCS TRUE
    0100000037 234.00 234.00 PCS TRUE
    0100000037 384.00 384.00 PCS TRUE
    0100000038 36.00 36.00 PCS TRUE
    0100000038 24.00 24.00 PCS TRUE
    0100000041 120.00 120.00 PCS TRUE
    0100000041 120.00 120.00 PCS TRUE
    0100000044 168.00 168.00 PCS TRUE
    0100000044 108.00 108.00 PCS TRUE
    0100000045 144.00 144.00 PCS TRUE
    0100000047 240.00 240.00 PCS TRUE
    What formula should i use to count every cell ?
    for example 0100000037 this Code is showing in 3 cells so what i ll do to calculate in cell a1=1 a2=2 a3=3 and so and so ?

  38. The "COUNTIF - COUNTIF to count numbers within a range" section is clearly wrong. If you just manually count the matches on the list, there are a total of 5.

    Not sure if your understanding of the function is wrong or just your visual aid, but since I can't get this to work based on your instruction I suspect it is the former.

    1. Hello Charles!
      You're right - the formula does not take into account the number 15. Thank you for your attention. I fixed everything

  39. Count in a single formula total A or B Or C
    where =COUNTIF(C1,"A") it can count only A

  40. Hi there,

    For my homework assignment I'm supposed to be using countif for data on earnings for various companies. In one column she wants me to be able to have each value say if it's over $1billion or below, and if it's below, by how much. I am confused on how to do this in one column. She also wants it to display text of my choosing, which I would say "Above" or "below"

  41. Hi i have a spreadsheet Songs and i need to use Simplex criteria to get the names of the row, which formula do i use?

    1. Hello!
      Please describe your problem in more detail. Write an example of the source data and the result you want to get. It’ll help me understand it better and find a solution for you. Thank you.

  42. Hi there! I am trying to combine these formulas so that both criteria must be true for it to be counted however as my ranges are different sizes this is problematic with the countif function. Is there a way around this?
    These are the two formulas which need to be combined:

    =COUNTIF('Volunteer Call Handler Form APRIL'!Q:V,"XXXXXXX - recent")
    AND
    COUNTIF('Volunteer Call Handler Form APRIL'!G:G,"Inbound")

    1. Hello Tilly!
      You can use the COUNTIFS function. But уach additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other. Read about it here.

  43. Hi, I want to count those items which have 3 letters + 4 numbers from below, how to count it please?

    ABC2001
    ABC2002
    ABC2003
    ABC2004
    ABC2005
    ABC2006
    ABC2007
    ABC2008
    ABC20A
    ABC20B
    ABC20C
    ABC2009
    ABC2010
    ABC2011

    1. Hello,
      Please try the following formula:

      =SUMPRODUCT(--NOT(ISNUMBER(--LEFT(C1:C14,1))), --NOT(ISNUMBER(--LEFT(C1:C14,2))), --NOT(ISNUMBER(--LEFT(C1:C14,1))), --ISNUMBER(--RIGHT(C1:C14,1)), --ISNUMBER(--RIGHT(C1:C14,2)), --ISNUMBER(--RIGHT(C1:C14,3)), --ISNUMBER(--RIGHT(C1:C14,4)))

      I hope it’ll be helpful.

  44. Sorry, not sure what happened to what I typed above. Edited:
    Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.

    When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 27.

    When I use =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 3.

    I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.

    Thank you.

  45. Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.

    When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4)-COUNTIF(I2:I21,"<="&O4) I get 3. This is also incorrect because I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.

    Thank you.

    1. Hello Adina!
      If I understand your task correctly, maybe the following formula should work for you:

      =COUNTIF(I2:I21," >= "&N4)-COUNTIF(I2:I21," > "&O4)

      or

      =SUMPRODUCT(--(I2:I21>=N4),--(I2:I21<=O4))

      If there is anything else I can help you with, please let me know.

  46. Hello,
    I am trying to count the instances where a particular string of text is found in column A, and the date in column D is within a specific range. Could anyone tell me why this isn't working?
    =COUNTIFS(Datasheet!A:A,"*theme*",Datasheet!D:D,">=6/1/2019", Datasheet!D:D,"<=6/30/2019")
    Thank you!

    1. Hello Madeline!
      Please try the following formula:

      =COUNTIFS(A1:A40,"*theme*", D1:D40,">="&DATE(2019,6,1), D1:D40,"<="&DATE(2019,6,30))

      Hope you’ll find this information helpful.

  47. Hi i just seek your assistance, i am trying to count the number of request i've send to a specific person in a day and also the number of answer i receive with them i day also. Here's my problem my formula doesn't count right. Here's my formula in Sheet1 (=COUNTIFS(March!O:O,">=" & Summary!B5,March!O:O,"<=" & Summary!B5+1)) details are from Sheet2 with a date and time format for March. Summary!B5 means the day today or next day. Hope you can assist me thanks.

  48. Hi there.

    I have data with students performance in a particular classroom.
    Data Columns: Names, Age, Gender, class/grade, Attendence, Performance status, performance status update.

    Options of performance status / update are: poor, good, excellent.

    I want to run some queries on this data in a summary table giving the following information:
    1. Count Attendance by: Age, Gender and Class/Grade
    2. Count Performance by: Age, Gender, Class/Grade whereby I count Performance Update when both Performance Status and Update have values and count performance status when Update is blank.

    Please help. I was thinking of using class/grade as a filter for the summary table such that I can switch between classes easily as well as having overall school summary

  49. This is FABULOUS! Thanks!

  50. I am trying to count the number of shifts a person works between a date range, but the shifts are on a 4 on, 4 off schedule.
    In a separate tab, I have the start & end dates of each of the shifts till the end of the year. How do I tell it to count the number of days that someone would work, on this shift, given their start & end date of employment.

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