Comments on: Google Spreadsheet COUNTIF function with formula examples

We continue exploring the possibilities of Google Spreadsheets with COUNTIF function. Learn why you may need it and how it can be applied to your daily work with the tables. Continue reading →

Table of contents

Comments page 8. Total comments: 391

  1. How do I count if for a range of multiple text entries. I.e.

    Cell A2 contains the text "dog"

    Cell A3 contains "dog, cat, cow, chicken"

    I want to perform a count if A3 contains A2 in the string of text.

  2. How do I create a formula that counts if one cell is greater than another?

  3. I think the post needs to be looked over for typos. I got lost in the very beginning. The post is below. And these are my questions.

    Do we count the sales in the West region, not Milk Chocolate?

    Why do I put the cursor in C3, which in the middle of the data of the first picture and contains "West" and try to enter the COUNTIF function there?
    Where is the range D4:B74 coming from?

    "Let's begin from the basics.

    We need to count the number of sales in the "West" region. Place the cursor in C3 and enter the equality sign (=). Google Sheet immediately understands that we are going to enter a formula. As soon as you type the letter "C", it will prompt us to choose a function that begins with this letter. And we select "COUNTIF".

    The first argument of COUNTIF is represented by the following range: D4:B74. By the way, you don't have to enter the range manually - mouse selection is enough. Then enter a comma (,) and specify the second argument - searching criteria.

    The second argument is a value that we're going to look for across the selected range. In our case it's going to be the text - "Milk Chocolate". Remember to finish the function with a closing bracket ")" and press "Enter".
    "

    1. Thank you very much for your attention to detail, Mate!
      We changed a few screenshots, but somehow totally forgot to adjust the text, so sorry about that!
      Fixed! :)

  4. Hi there,

    I've got a COUNTIFS formula which I'm building to try and accommodate variable criteria, i.e. dates.

    I want to code a COUNTIF with 'greater than or equal to' to a date cell, but it doesn't seem to want to read that cell. I've tried formatting the date cell in different ways but not working.

    For simplicity I've reduced it to a COUNTIF.

    None of the following work:
    =COUNTIF('Student Tracking Report'!AS:AS,<=D$5)
    =COUNTIF('Student Tracking Report'!AS:AS,"<="D$5)
    =COUNTIF('Student Tracking Report'!AS:AS,"<=D$5")

    This does work, but won't be variable:
    =COUNTIF('Student Tracking Report'!AS:AS,"<=43344")

    Thanks for your help.

    Marcus

    1. Hi
      The solution is pretty easy, you've just missed the "&" symbol before the cell address. Try the formula below and enjoy ;) =COUNTIF('Student Tracking Report'!AS:AS,"<="&D$5)

      BTW, it is in this article in the "COUNTIF Google Sheets for less than, greater than or equal to" part.

  5. Any know how to COUNTIF from anothes sheet page into the same file?
    Thanks.

    1. Create the formula on one sheet, but refer to cells from another sheet :)
      It will look like this: =COUNTIF(Sheet1!A1:A10,">20")

  6. Hi,

    Could you help me with the following problem:

    I want to count the cells in a range (column) which contains any characters inside. Cells has time format, and an 'if' formula: =IF(OR(B10="",D10=""),"",D10-B10-$M$4)

    I tried with countif(range, "*") -> it interestingly count cells which are empty, contains just the formula, but no value, as the corresponding Bx or Dx was empty. And does not count any which has a value (8:00 for e.g.) out of the formula.

    With countif(range, "") I had exactly the same result...

    Am I doing something wrong?
    Please help!

  7. Inx:
    Where Fruit is in F2:F5 and Color is in G2:G5 the formula looks like this in an empty cell:
    =COUNTIFS(F2:F5,"Apples",G2:G5,"Blue")

  8. Hello,
    Could you help me with a formula for the following:
    I need a count of the number of people who answered one answer in the first column, who also answered a specific answer in the second column.
    Example:
    People who choose "Apples" as 'favourite fruit' who ALSO choose "Blue" as 'favourite colour'.

    Thank you

  9. I have results from a google form.

    In the form responses, I need to separate the data in several ways.

    1. Responses/multiple choice answers by Class/Teacher(which is a column/question in the Form) and by the entire school.

    2. Each response has been assigned a letter value which is an abbreviation for a response type. (I've already used Find & Replace to change the text answers to the letter value, now I just need to get and graph the counts of each of the response types.)

    3. Responses by student.

    There has got to be a way to separate into three additional spreadsheets using the information from the initial form response spreadsheet.

  10. What if I need to match a text with case sensitivity? Looks impossible with this formula, but probably shouldn't be.

  11. I have a column of dates (B3:B2) and I wanted to get a count for the following criteria:

    Under 2 years
    2-5 Years
    6-10 Years
    Over 10 years

    I have used this for the under 2 years and it is not returning the proper number:

    =countif(B5:B53,"<today()-730")

    1. I know this is very old, but it might help someone else.

      I was trying to build a very similar formula, and I also was not having any luck. However, I found someone else mention that you can 'combine' formulas by using the & sign, so I tried this and it worked.

      =countif(B5:B53,"<" & today()-730)

      Apparently the today() function shouldn't be in quotes.

  12. Hi,

    I'm looking for a formula for 4 different countries (Ireland, Denmark, UK & Sweden) so I can keep track of my days in a country throughout the year. I'm using the below,

    =COUNTIF(W3:W30,"Ireland")+COUNTIF(W3:W30,"Denmark")+COUNTIF(W3:W30,"UK")+COUNTIF(W3:W30,"Sweden")

    I've tried using *UK* etc but not working. I have this formula in another column and it works fine?? Help :)

  13. Hello

    I need some help in writing a query/script for excel/googlehseets that will take a word and link/URL from where I want to find the word then the query/script will count the number of repeating time of given word and place that number back into excel/googlesheets.
    Anybody can help me in this.

    Thanks in advance.

  14. So I have a sheet where I want to track how many participants answer certain questions. There are 11 questions and each question has its own reference cell. The cell beside the participant will have something like 1,2,3,7,9 in it, but I can't figure out how to count only one of those numbers while excluding the rest. Any suggestions?

    1. Just to clarify: are the respondents in the first column, the response to the secomd question in the secomd column, and so on for each of the eleven questions?

      1. A coworker of mine helped me figure it out: =COUNTIF(B$2:B$30, "*"&I2&"*")
        Putting the cell that contained the number I was trying to find in between "&"'s and putting the wildcards in ""'s joins it to whatever before and whatever after. This is SO INCREDIBLY helpful! posting it here for all to see :-) this means you can have multiple variables in the same cell and still be able to parse that data!

      2. (oh man...I just looked at how my reply was formatted...it looks awful. Can you follow it at all?)

      3. So, the columns and stuff would look like this

        NAME QUESTIONS ASKED QUESTION # of PARTICIPANTS
        Joe D 1,2,3,7,9 7 [insert formula here]

        (Does that layout make sense?)

  15. Hello,

    could anyone help me with the following task? My dataset contains (among other) these columns: (A)Date of the 1st check, (B)date of the 2nd check, (C)Date of the 3rd check and (D)Status. Let's say Status could be "Waiting", "Not OK", "OK". At the end of the day I need a number of cases, where Status="OK" and at least one of the Checks (1st, 2nd, OR 3rd) were done today. If there were just one check, I would use: countifs(D:D;"OK";A:A;"26.02.2018"). But I am not sure how to solve out the task, where one of the required criterion (today's date) can be met in one of the several columns.

    Thanks a lot

    1. Greetings, Ondra!
      Simply modify the single-column version of your formula to include the other columns.
      =countifs(D:D;"OK"; A:C;"26.02.2018")

      To make the formula a bit more versatile, you can replace the hard-coded date with, for example, the TODAY() function or a cell reference that contains a date.
      =countifs(D:D;"OK"; A:C;today())
      =countifs(D:D;"OK"; A:C;E2)

      To be really cool, you could use something like the last formula, then find items checked today by entering
      =today()
      in cell E2. :-)

  16. Just wanted to say thank you. This was the clearest depiction of how to reference other cells within a COUNTIFS formula that I could find.

  17. HOW TO COUNT HOW MANY CUSTOMER ORDER MILK, DARK ETC. THANKS

  18. Hey,
    could you give me a hint how to use countifs with the following table which contains results of different soccer games;

    as an example
    A1:A5 Name Player 1
    B1:B5 Goals Player 1
    C1:C5 Goals Player 2
    D1:D5 Name Player 2

    Now, I wanna count the wins (more goals) of a specific player:
    =countifs(A1:A5, "Player1", B1:C5, (B1:B5 > C1:C5))

    Unfortunately, the second part throws an error :(

    1. Hey, Martin,

      if I understand your task correctly, please try the following formula:
      =SUMPRODUCT((A1:A5="Player 1"),(B1:B5>C1:C5)

      If it doesn't work, please go to File > Spreadsheet settings > Calculations, and make sure that Iterative calculation option is On.

      Hope this helps!

  19. Is there a way to use =countif to count if there is any value in the identified cell/cells? For example:
    | | -should not count
    |a | -should count
    | | -should not count
    |ab| -should count

  20. Hello!

    Is it possible to use COUNTIF to determine how many cells do not contain a particular letter? I understand how to use the formula when dealing with numbers, but mathematical symbols don't seem to work the same way with letters.

    So, for example, if I want to know how many cells in A1:A25 do NOT contain G, how would I write that?

    Thank you!

    1. Hello,

      Please try the following formula:
      =ArrayFormula(SUM(IFERROR(FIND("G",A1:A25)=0,1)*1))

      Hope it will help you.

      1. Gennady,
        You had no formula in your response.
        I have the same obvious issue as Rachel. I want to count cells which are not "N".
        Thanks,
        Brad

        1. Brad,

          For some reason, Gennady's formula wasn't displayed. I've edited the comment and now you can see the formula. Please try to adjust it for your data.

  21. in google sheets, i'm trying to count the number of occurrences where column W=YES, IF column P=5. How can I write this formula?

  22. Is it possible to use, as a criterion, the background color of a cell in the countif function? For example, in column A (say from A5 to A10), there are two types of cells: those that have a green background color and those that are white. The range of cells to be added is in column B (say B5 to B10). I would like, in cell B11, to sum cells B5 to B10 if they are next to green cells. In cell B12, I would like to do the same for the cells next to the white cells.
    I thought using Apps script, but I would prefer to use simple formulas.
    Thank you.

    1. [resolved]Finally, I decided not to use the background color as a criterion for selecting the cells to be added. I found a simpler solution, based on the content of the cells using the sumif function. Specifically, I used the following formula in cell C268:
      SUMIF ($A5: $A267; "* (AFR)"; C5: C267)
      In other words, instead of using the color, I select the cells whose content ends with the 5 characters: (AFR)
      Merci

  23. What is the countif formula in goggle sheets if the possible choices included are as follows: >, <, or =. I have used the following formula and keep getting an answer of 0 students with the correct answer. However hundreds of students have the correct answer.
    =countif(BL3,"<")

    1. Hello, Mary,

      If I understand your task correctly, there's something missing from your formula. You need to count if one value is greater/less than or equals to some other value. Also, please make sure to indicate the range of cells correctly in the first argument.

      So, if your data is spread across B1:L3 and you need to count all the values that are less than 3, your formula should look like this:
      =COUNTIF(B1:L3,"<3")

      Please adjust your formula according to your data, and it should work :)

  24. I'm trying to search for the number of occurrences on a particular date. The info in the cell is the data and time stamp. How would I set up the formula so it is only pulling the date?

    1. Hello, Lisa,

      supposing, that the date an time are in A1, use the following formula in the cell where you want to return the date, A2 for example:
      =DATEVALUE(A1)

      Then select A2, and in the menu choose Format > Number > Date. You will see only the date from A1 in this cell.

      Hope this is what you need!

  25. what is the formula for.

    if a1 is less than b1, put good
    if a1 is greater than or equal to b1, put bad
    if a1 is less than b1 but greater than c1, put critical

    put this in one formula?

    1. Hi, Loise,

      try this formula:
      =IF(and(A1<B1,A1<=C1), "good", IF(AND(A1>B1,A1=B1), "bad", IF(AND(A1<B1,A1>C1), "critical","")))

      You can read more about IF function in Google Sheets in this article.

      Hope this helps!

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