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

  1. I am trying to figure how to count an item

    ex. I have a set of dates throughout the year in 1 column and I have another column with a word in it; ex. open or closed.
    I am trying to see how I can count either open or closed from the date column?

  2. what is the difference between =COUNTIF('sheetName'!B2:B15, "=PASS") and =COUNTIF(sheetName!B2:B15, "=PASS")?

    thank you
    branko

    1. Hi Branko,

      If the sheet name includes spaces or non-alphabetical characters, it must be enclosed in single quotation marks. If the sheet name includes only letters, numbers and underscores, single quotes are not needed. For example, to refer to "Sheet1", you'd use the reference Sheet1!B2:B15. To refer to "Sheet 1", the reference is 'Sheet 1'!B2:B15.

      For more information, please see How to refer to another sheet or workbook in Excel.

  3. I have a large excel file with a lot of data.

    I know how to delete the duplicate values but keep one value.

    I need to learn another technique.

    For example, a particular column repeats some data twice, some data thrice, and some data for four or five times.
    I want to delete the first entry only. I don't want to delete the second, third or fourth entry. That means only the first entry or first data will be deleted, but all the second, third, fourth or the next entry will exist.

    Will you please tell me how I can do this?

    1. Hello!
      To determine the 1st occurrence of the duplicate, write down the formula in the additional column:

      =IF(AND(COUNTIF($A$2:$A$9, A3)>1,COUNTIF($A$2:$A3, A3)=1), "1st occurrence", "")

      In this case, your list begins at cell A3. Copy the formula down the column as needed.

  4. Hi, Can u help me
    ive got several problems with my excel worksheet>
    im trying to figure out how to count some data with random data like this :

    Dates 02/01/22 03/01/22 04/01/22 05/01/22 06/01/22 07/01/22 08/01/22
    Name
    Yoga CT PL CT CT PL DL DL

    and i want to get (CT) count data with range number that i provide from another cell, so ijust need to fill my range date cell and get total data number :
    A1 A2 A3
    Data Range 02/01/2022 04/01/22

    Thank u so much
    appreciate

  5. The tutorial Excel COUNTIF for blank and non-blank cells contains numerous errors.

    COUNTIF(range,"*")

    The tutorial states that this formula counts only cells containing any text values. It does not mention that it also will not count cells containing null text values that are the result of a formula.

    Consider a cell containing the formula =IF(D52908="kMDItemUserTags = (",MID(D52909,5,99),""). Note that the result of this can be, in fact usually is, null, i.e. zero in length. When the condition is false, the result of the COUNTIF function is wrong.

    This can be tested by having a cell with the formula ="" in the range of the COUNTIF function.

    COUNTIF(range,"")
    and
    COUNTIF(range,""&"")

    These also fail when a cell within the range contains a null value that is a result of a function.

    In the screenshot show in the tutorial, placing the formula ="" in cells C4 and C8 will illustrate this.

    COUNTIF(range,""&"*")

    Again, This also fails when a cell within the range contains a null value that is a result of a function.

    COUNTIF(range,"")

    Finally, a formula that works when the range contains formulas generating null values.

    But why use the COUNTIF function at all? Just using the COUNTBLANK(range) function will be simpler and more descriptive.

    But, all these (COUNTIF and COUNTBLANK) functions also fail if the range contains cell which contain one, or more, spaces, whether keyed in or the result of a formula. In other words, the length of the cell is greater than zero but the data consists only of spaces. Excel, apparently, does not consider spaces to be blank.

    1. Hello Mark,

      Thank you for your feedback. I will try to clarify the two main points.

      Zero-length strings. In terms of Excel, an empty string ("") is a text value - ISTEXT returns TRUE for it. The COUNTA function also regards zero-length strings as non-empty cells. Given the above, it's incorrect to say that COUNTIF formulas fail in this scenario as their results are consistent with the results of other Excel functions.

      Spaces. Like zero-length strings, spaces (and other non-printable characters) are text values. Therefore, cells containing only spaces are not considered empty and are not counted by COUNTBLANK or analogous COUNTIF formulas.

      Why do we use the COUNTIF function for counting blank/non-blanks? At the very least, to show that Excel provides different ways to accomplish the same task :) Of course, there is also a practical application - knowing how to express such criteria, you can build more sophisticated COUNTIFS formulas, say, to count rows that have blanks in one column and non-blanks in another.

  6. Hi,

    I am trying to create a dynamic formula for the below table to count no of invoices(unique).. When I use countif function the output which I am getting is counting all the row's. For Eg Doc # 20212463 this will be counted as 2 instead of 1

    I am looking for a formula like this - in the month of Sep on Thursday at 12 .00pm how many unique invoices were created. I really appreciate if you can help me with this.

    Doc # Wk day Time Month
    20212463 Tue 16:00 Aug-21
    20213081 Thu 19:00 Oct-21
    20212463 Tue 16:00 Aug-21
    20212704 Thu 12:00 Sep-21
    20212960 Wed12:00 Oct-21
    20212704 Thu 12:00 Sep-21
    20212705 Thu 12:00 Sep-21
    20212706 Thu 12:00 Sep-21
    20212707 Thu 12:00 Sep-21
    20212708 Thu 12:00 Sep-21
    20212488 Fri 17:00 Sep-21
    20212702 Wed 16:00 Sep-21
    20213074 Wed 14:00 Oct-21
    20212489 Fri 18:00 Sep-21
    20212292 Fri 22:00 Aug-21
    20212382 Sat 20:00 Aug-21
    20212788 Thu 18:00 Sep-21
    20213086 Fri 17:00 Oct-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21

  7. Sir
    can you please send this excel file on email

    COUNTIF in Excel - count if not blank, greater than, duplicate or unique

  8. Hi,

    Pls suggest me a formula if i want to count total particular time (hr:min) from given range of time.

    i.e. if somebody have performed shift duty from evening 20:00 o’clock to morning 8:00 o’clock and i want to count hours from 22:00hrs to 6:00hrs for that particular shift !

    So what formula will come there ?

    Thanks.

    1. Hi!
      If the end time is longer than the start time, then you need to add 1 day to their difference

      =IF(A1 > A2,A2-A1+1,A2-A1)

  9. Hi, please could you help. I am trying to count: I have a document of customer sales on my store,

    column A is style codes/skus (which will be duplicated). column b is units sold,

    I want to count the units in column B when they match a specific style code in column A so that I can see how many in total sold of a specific style.
    I have read this doc thoroughly and cannot seem to find a way to work it.
    many thanks!

  10. Hi!

    I need to count the number of countries from a list of employees, excluding those duplicated employees:

    For instance, I have three rows with the same employee (190175) from Spain, but I just want it to be counted once based on the empoyee ID.

    The formula I have is =COUNTIF($T$2:$T$30,'Spain'), but I dont know how to exclude those duplicated rows with the same ID. Could you help me?

  11. Hello I am trying to write into this equation to IGNORE blanks.

    =COUNTIFS(C3:C10000,E3:E10000,D3:D10000,">=5000",A2:A4409,"<=5999")

    Any help is appreciated

  12. I want to write the formula for selecting the value of BMI with criteria between 18.5 > 24.9-second criteria for "males". Please guide me.

  13. Hi Sir, good day.
    Can you give me an excel formula on below scenario:
    ColumnA-dates

    ColumnB-names

    I would like to have number of days captured(ColumnA) under specific name(ColumnB).
    Thanks!

  14. Vendor - Pay Type
    RED - CASH
    RED - CASH
    BLUE - CHECK
    RED - CASH
    YELLOW - CASH
    BLUE - CHECK
    YELLOW - CASH
    RED - CASH

    I need help figuring out the formula where the end results would be: CASH=2, CHECK=1.

  15. I just need count one column name and second one is date. name is duplicate in one column but date the increase day by day. I need count between 4 days repeated count.

    Name Date
    Abc 8/25/2021
    BCS 8/25/2021
    DEF 8/25/2021
    FRG 8/25/2021
    ABC 8/22/2021
    BCS 8/24/2021
    DEF 8/23/2021
    FRG 8/21/2021
    TYX 8/20/2021

  16. Hi!
    Im curious about the following scenario:
    I have a range of data that will sometimes include blank entries due to an internet intermittent connection. I need to count the number of instances that the data is below a given value, i.e. "<=5". The formula will not count empty cells as being equivalent to zero, and I'm wondering if that has to do with their cell type.
    I.e. the cells are currently registered as "General" format, not "Number" format, and I'm wondering if this would change the output of the formula.
    I don't want to necessarily change it, but I want to know how I should in case I need to adjust my report.
    To reiterate: The formula I am using is registering the cells with values correctly, but the empty cells are acting as a special case, and I want to know how Excel is interpreting their value currently. It is a CountIf formula, not CountA or CountIfs.

    1. Hi!
      Excel interprets blank cells as either blank or zero. It depends on the formula. But you did not specify this formula.
      Pay attention to the following paragraph of the article above — Count if blank or not blank.

  17. I need to do conditional formatting based upon 2 columns.
    I have 4 columns: 'in/out'; 'grade'; 'name'; 'reason'
    Basically if someone's name shows up 3 times with each time it shows up with"in" being chosen it needs to be highlighted. if someone's name shows up 3 times with each time it shows up with "in" was only chosenon 1 or two of the times it should not be highlighted

  18. Thanks - some great tips.

    In a spreadsheet, I want to count if all cells Sheet1!A2:C51514 are blank. When I do this formula =COUNTBLANK(Sheet1!A2:C51514) it seems to add all the blank cells in Sheet1!A2:C51514 together.

    BUT I just want to know if ALL three cells in Sheet1!A2:C51514 are blank, not add all the blank cells together.

    So when I filter I get 38,538 blanks ie the cells in column A are blank and the cells in column B are blank and the cells in column C are blank. This is the right number because it is not adding the blank cells together.
    =COUNTBLANK(Sheet1!A2:C51514) gives me 141,116.

    Do you know a formula to count if all the cells (A2:C51514) are blank?

    Should I use =SUMPRODUCT((COUNTIF(Sheet1!(A2:A51514)=” “)+(COUNTIF(Sheet1!(A2:A51514)=” “) ?

  19. i want to count the words like {"PL","HOL","CFL"} as 1 and {"HD","CFL-HD" } as .5 appearing in a row if the date on the other row is greater than today()
    also if the date is less than today()

    =SUM(COUNTIFS($C$1:$AG$1,"="&TODAY(),$C$2:$AG$2,{"PL","HOL","CFL"}))

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

      =SUM(COUNTIFS($C$1:$AG$1,">"&TODAY(),$C$2:$AG$2,{"PL","HOL","CFL"}))+SUM(COUNTIFS($C$1:$AG$1,">"&TODAY(),$C$2:$AG$2,{"HD","CFL-HD"}))*0.5

      I hope this will help.

  20. Good day

    Please please assist

    I am trying to sum up the hours lost due breakdown or other reason in that particular reasons column.

    Column B: shifts, either morning, afternoon or night shift

    Column k: time they actually started work

    Column L: reason of starting late like breakdown

    Morning shift suppose to start work at 08:30, and afternoon shift 14:30 and night shift 21:30

    I'm trying to get hours lost per particular reason.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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