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

  1. Hello!

    I am in a pickle and could use some help.
    With what kind of formula can I count years?
    I have a list:
    1 year; 0 months
    2 years; 2 months
    1 year; 0 months
    0 years; 4 months
    3 years; 3 months
    1 year; 0 months
    And so on.
    I would like to count them like this
    <1 year; 0 months
    1 year; 0 months <2 years 0 months
    And so on

  2. I Want to count if the value is =>6 count 6,if Value is 7-15 count 10, if value is 16-30 count 15, if value is 30-90 count 20 and if value is 90-200 count 50, and if value is 200 > count 100

    DOH Count
    15 10
    23 15
    0 6
    107 50
    50 20
    239 100
    5 6

    would you please help to solve this issue?

  3. How to count only Dates in a column withh dates and names

  4. Hi,

    I've been looking through the blogs about COUNTIFS, VLOOKUP, IF and DATES, but it still puzzles me. I'm in need of the formula to seek for the latest date (Column A) on a specific items (Column B) for its amount (Column C) to show up in a new column. For instance, I would required the latest amount of Item A on 5/3/22, which is supposed to come back at 6. Anything after 5/3/22 should not reflect in the new column.

    Column A
    2/3/22
    4/3/22
    5/3/22
    5/3/22
    6/3/22
    6/3/22
    6/3/22

    Column B
    Item A
    Item B
    Item A
    Item A
    Item A
    Item A
    Item B

    Column C
    4
    4
    5
    6
    5
    3
    5

      1. Hi,

        I've gone through some checking and found out that the XMATCH function only applicable to Excel 365 above. I'm currently using an Excel 2019 which did not support the new function. Would hope that there is a formula to reflect the previous formula given in Excel 2019.

        Thank you

        1. Hello!
          To find the last match in a range of data, select the row with the highest row number using the MAX function. Use this array formula:

          =INDEX($C$1:$C$7, MAX(($A$1:$A$7=F1)*($B$1:$B$7=G1)*ROW($A$1:$A$7)))

          Press Ctrl + Shift + Enter so that array function works.

          1. Hi Alex,

            Thank you for the wonderful formula. The formula works perfectly fine, but only if the first row starts with Row 1. If I were to start it on other rows, the formula will not come back to the designated results be it array function or not. For instance, if I started it out with Row 2, an array function will come back with 5 and without the array function will come back with 4. What was the issue that caused for the differences?

            1. Hello!
              Subtract from the row number the number of rows before the start row. If it starts on line 2, then subtract 1.

              =INDEX($C$2:$C$8,MAX(($A$2:$A$8=F1)*($B$2:$B$8=G1)*(ROW($A$2:$A$8)-1)))

              1. Hi Alex,

                Fantastic! Thank you once again for your assistance. Very much appreciated.

      2. Hi,

        I've tried on the formula given but it does not come back with any number. Instead, it only shows "#NAME?" in the column.

  5. How to use COUNTIF to count the number of times a cell appears in a range.
    For Example: in cells C10:C33 I have equations, and I want to know within that cell range how many times F2 appears in the equation.
    This is to track an expenditure “automatically”

    If COUNTIF cannot be used for this then what is your suggestion?

    1. Hello!
      If I understand the problem correctly, get the text of the formula using the FORMULATEXT function and find the text "F2" using the SEARCH function.

      =COUNT(SEARCH("F2",IFERROR(FORMULATEXT(C2:C20),"")))

      Hope this is what you need.

      1. Thanks for your reply, interestingly it doesn’t want to acknowledge that we’re doing a range. I tested it on single cells and it works, giving me a value of 1 when it contains “F2” and 0 when it doesn’t.

        Once I introduce the range of C10:C33 it just gives me a value of 1 instead of counting the entire range.

        It’s a problem with the formulatext only accepting the first cell in the range.

  6. I am going crazy and am new to spreadsheets.
    I am using
    =COUNTIF($D$53:$D$82,"Yes")/COUNTIF($D$53:$D$82,"*")
    This range is 31 cells and represents days in the month. The result is reading as [0/32 days] - which is very close to what I need except the range should read /31 days. I'm boggled.

  7. I tried using the countifs formula to know the number of items within a specfic date range..

    Apple jan-april total 100

    It worked well.

    But when I tried to use the same formula in finding the total number of apples per month. It doesnt seem right.

    Because when i tried to add the number of apples from jan - apr, im not getting the total 100 from the 1st formula.

    Can someone pls help?

    1. Hi!
      I don't know what formula you are using and can't see your data. Please re-check the article above since it covers your task. Or describe the problem in more detail.

  8. Is there a way to count only the cells in a row that have visible data? Meaning it doesn't count cells with formulas in it when the formula is telling the cell not to show anything, like by using "" ?

    Thanks for your consideration.

    1. Hello!
      If I understood correctly, check the value "" in the cell. For example,

      =COUNTIFS(A1:A5,"<>",A1:A5,"="&"")

  9. Thanks for your very useful blog.
    I just wish to know how you display the results of countifs. for example if I wish to find numbers >= 5 in a long list of numbers, countif return their count but I need to display also the numbers or list of numbers which is/are greater or equal to 5.

    Best Wishes
    Ali

      1. But filter function didn't work in excel 2016 any other suggestions?

      2. Thanks a lot.

  10. Hi. How do I get a count for years of service when my cells have decimals (must keep the decimals)? Example: If I have services ranging between 0-55 years and I want to calculate how many have worked for 5 years, but I have some at 5.1, others at 5.4, 5.7, etc. How do I calculate how many of employees are celebrating their 5 years (including 5 to up to 5.9, but not 6) this fiscal year?

    I tried these: =COUNTIF(J2:J113,">=5*=5=5"); =COUNTIF(J2:J113,"*~5*"); =COUNTIF(J2:J113,"~5*"); =COUNTIF(J2:J113,"~5.*"); =COUNTIF(J2:J113,"*5.*"); =COUNTIF(J2:J113,"5.?"); =COUNTIF(J2:J113,"5."); =COUNTIF(J2:J113,"5"); =COUNTIF(J2:J113,"=5""=5""=5,<=6"), and countless others, but they don't work.

    What am I missing? I just want to know how many people to celebrate in increments of 5s at our facility, but can adjust the formula once I have the 5 years ones.

    Thank you in advance for your help.

    Blessings.

    1. Hello!
      To count the number of values in an interval, use the COUNTIFS function with two conditions:

      =COUNTIFS(J2:J113,">="&5,J2:J113,"<"&10)

      Or use two COUNTIF functions:

      =COUNTIF(J2:J113,">="&5)-COUNTIF(J2:J113,">"&10)

    2. Oh my goodness! Could it be =COUNTIF(J2:J113,">=4.5")-COUNTIF(J2:J113,">6") ?!
      As soon as I posted my frustrated question, I thought of this one. Is this correct?

  11. Hello,
    This Formula works =COUNTIFS(COUNT, B26, CO, "FSC",STATUS, "OVERDUE") but I want to a count of how many OVERDUE are over 120 Days.
    B26 = the Category
    CO = the B Column
    STATUS = the F Column and OVERDUE is in that Column
    DAYS = Column G
    I want a count of the category specified that is in the CO specified that has a status of OVERDUE with days that are greater than or equal to 120.

    Any Ideas?

    1. Hello,
      Thank you for your reply! It is a number and I want to count how many with that name have a number greater than higher than 120 in column G. and I have read this multiple times and I cant get it to do what i want. I'm not that smart, which is why I asked all of you (The Smart Ones) the question. I tried =COUNTIFS(COUNT, B27, CO, "FSC",STATUS, "OVERDUE",">="&120) and it says "You've entered too few arguments for this function". I have a cell that counts if the name in B27 that has a company "FSC" that has a Status of "OVERDUE" and it works but I need to know how many of those with that name, company, and status are equal to or over the number (120) in a different column.

      Thank you

        1. Thank you for you response!
          I tried it with no luck, it says #VALUE! where the count should be. I tried this a couple of different ways with the same out come.

      2. Just to be clear I I have tried more that one string. I have tried multiple in different variations to include dates with =COUNTIFS(COUNT, B27, CO, "FSC",STATUS, "OVERDUE","<="&TODAY()-120) and everything in the article that made since to what I was trying to accomplish ( In my option, I very well could be wrong and answer is staring me right in the face ( I've staring at this spreadsheet for a couple weeks now)).

    2. Hi!
      Read the article above carefully, it contains the answer to your questions.
      I don't know how the days are written in column G. If it's a number, then the condition is ">="&120
      If it is a date, then the condition is "<="&(TODAY()-120)
      For more information about counting with multiple conditions using the COUNTIFS function, see this article.

  12. Hi, I am looking to compare first and last names from two different sheets. First sheet is our master registration with over 500 names on it. Second sheet (and subsequent sheets) is/are individual event(s) that someone is signed up for.

    Column A is last name
    Column B is first name
    Column C on "Master Registration" sheet is the first event
    Column D on "Master Registration" sheet is the second event....and so forth

    I would like to have Columns C, D, etc. return a value to indicate the different events that someone is signed up for, based upon second, third, fourth sheets.

    The issue I am having: first and last names are separated into separate columns...

    Any help would be greatly appreciated. Thank you very much.

    1. Hello!
      If the second sheet (and subsequent sheets) also has columns with first name and last name, then try using the guide: Excel INDEX MATCH with multiple criteria.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  13. how this if(countif(A$1:A1,A1)=1 formula works

    Where (A$1:A1) & (A$1:A1,A1) is used

  14. I am trying to set up a vacation tracker where every V for vacation is counted as 1 and every .5V is counted as a half day. I tried to do a countif, but couldn't get it to work.

    1. Hi!
      Count with COUNTIF first with V, then with the second formula with 0.5V. Multiply the second result by 0.5 and sum both results.

  15. Hi Alexander!

    This may sound really silly, but here's the question: I have one row of numbers where I am adding all numbers in the row

    1 2 3 4 5 6 7 8 9 10 11 12 etc

    How do i get the count of cells until the sum in the row is less than or equal to 21?

    The answer of count must be of course 6

      1. Thank you Alexander!

        Just in time for handing the excel which is due today!

        Have a good one

  16. Hi there,
    Lets say I have this situation
    A B C D E
    A1 B1 C1 D1 E1
    A+ B+ C+ D+ E+
    A- B- C- D- E-

    I would like to get all values of letter A
    please help.

  17. Hello! Sorry, why in the formula for calculating COUNTIF within a range
    =COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15") there's a greater-than sign before 15? Shouldn't it be a less-than sign (<)? Thanks

    1. Hello!
      Unfortunately, you are mistaken. The formula counts values greater than 5 and then subtracts the number of values greater than 15. As a result, we get the number of values greater than 5 but less than 15.

  18. Will countif function counts cells with formulas in it?

    1. Hi!
      Of course. The COUNTIF function counts values that are written manually or obtained using formulas.

  19. Hi! I have been working on a formula and I wanted to ask if there is a workaround it. Columns C-O has data validation in it (Yes/No). For Column P (Remarks) wanted to do a sanity check so I have this formula =IF(COUNTIF(C1:O1,"No")>0,"Incomplete","Complete"). The default remark is complete - I wanted to know if the default remark can be blank instead of complete? Thank you.

  20. Hi,
    Please help with this task.
    I want to list the names who have not participated but registered for the period of last week (which starts on Monday) by comparing the list of participants.

    Column A: contains participants
    Column B: contains the date
    Column C: registered members

    Thank you

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  38. 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)=” “) ?

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

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

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

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

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

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

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

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

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

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

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

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

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