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 →

Comments page 6. Total comments: 393

  1. I feel I am missing something in my code. perhaps you can help! My intention is that when 2 criteria (column n which holds a tag type and Column m that holds a tag number) both have duplicates it would change the color on column n on the duplicates. I have done this before with one column =countif(B:B,B1)>1 and that works, but trying to do 2 columns it's not working. what am i missing? Two codes below are ones I have used to try to get to work.

    =((countif(N:N,N1)>1)+(countif(M:M,M1)>1))

    =COUNTifS($N$2:$N, ">1",$M$2:$M ,">1")

    1. Hello April,

      If you're trying to color complete row duplicates, a formula like this should help you:
      =COUNTIFS($M$2:$M;M2;$N$2:$N;N2)>1

  2. Hi,
    We're using sheets as a weekly time table for project work management. We write down which project we worked on at what time. I'm trying to summarise the amount of time worked on each project at the end of the month.
    we have 2 products that use the same project names, with the product acronym ahead of the project name (IE-AB Cars\CD Cars).
    Each sheet represents a week.
    Using =COUNTIFS('2-8.8.2020'!A3:G22,"*AB*",'2-8.8.2020'!A3:G22,"*Cars*")
    This works fine when I refer to one sheet\week at a time. but trying to add all sheets\weeks doesn't work

    thanks

    1. Hi Eli,

      Could you please let us know what your formula returns exactly when it doesn't work? Is it '0' or some specific error?

      1. Hi Natalia,
        It returns'0'

        1. Thank you for replying, Eli.

          Since COUNTIFS "returns the count of a range depending on multiple criteria", '0' usually means that there are no records matching all your criteria.

          However, if you see matches without the formula and are sure the result should be different, please share an editable copy of your file along with the formula that doesn't work with us (support@apps4gs.com).
          Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. We'll look into it and see what may be going wrong.

  3. I need some help.
    I have a calender with an allocation of 2HC per day to go on leave
    I would like the first 2 names that are input in the day to be left in its original format but with every additional names added into the particular date range will be highlighted.

    I'm currently using =countif($F$8:$F$11,"*")>2 but this will highlight every column in my range for as long as the input is more than 2.

    Is there any way to go about highlighting only the names that comes after the 2nd entry?

    Thanks ^^

    1. Hello Farah,

      Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) a small example of your source data with the rule you're trying to apply 2) and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      Thank you.

  4. can we count how many operators does a cell have in spreadsheet

  5. Little help? countifs two arguments don't work for me

  6. Good afternoon, I have a question.
    I have several texts in different cells. The texts are similar. How do I know if they are the same? And if they are not the same, how do I know what percentage of words are repeated? And based on that percentage, can I declare a color range?

    Then, I need to import from one sheet to another according to that percentage. If the percentage is high, it is not copied, if it is low, it is copied.

    thank you... ♥

    1. Good afternoon, Martin,

      I'm afraid your task is rather complex so I can't suggest anything in particular. However, you can share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. We'll look into your task and see if we can help.

  7. Sheet 1
    Col A1 58+1 BOXN
    A2 42+1 BCN
    A3 57+1 BCNHL
    A4 58+1 BOXNHL
    Sheet2
    Col A1=( Count of BOX/BOXNHL)[=2]
    Col B1=(count of BCN/BCNHL)[=2]
    HOW TO WRITE FORMULA

  8. Is it possible to use countif with ValuesbyColor. IE I'd want to know how many times "John" appears in either red or blue cells

    1. Hello Tomi,

      Yes, you can use the valuesByColor formula in the COUNTIF. However, one formula can process only one color at a time, so COUNTIFS won't work here. Please do the following:

      1. Count each color separately.
      2. Add each valuesByColor formula for each color into individual COUNTIF formula to count 'John' only:
        =COUNTIF(valuesByColor("#f4cccc", "#000000", Sheet1!A1:A10),"John")
      3. Sum the results.

      1. Thanks that worked! I also have a follow up, does it work with sumif?
        I've tried
        =SUMIF(valuesByColor("#e06666", "#000000",Sheet1!$B$2:$B$999"*John*",Sheet1!$D$2:$D$1000))
        The second range ($D$2:$D$1000) counts time so it should create how long time John has accumulated in all the cells with that color. But the error it gives me that there is only 1 argument

        1. Tomi,

          The SUMIF function requires different arguments, so placing them in the same order as for COUNTIF won't work. Also, in your formula, you put the condition for John and the range with time inside the valuesByColor, this also shouldn't happen.

          If I understand it correctly and you need to sum times based on two conditions: 1) if there's 'John' and 2) if a cell is coloured specifically, it is SUMIFS that you need (sum on multiple criteria). But at the moment valuesByColor doesn't work within SUMIFS and COUNTIFS.

  9. Thank you all for this content. I have one column of data where each cell is assigned a number between 1 and 13. I'm trying to find how to know the probability that any one number follows another. For example, what is the probability that a 7 is followed by another 7. Or what is the probability of the sequence 4, 9, 13. If anyone can help with this formula I would be more than grateful.

  10. I need help for Google sheets formula.

    if the percentage value of F4 > C4 = "good"
    if the percentage value of F4 < C4 = "bad"
    if the percentage value of F4 = C4 = "same"

  11. Hi,
    I have an 'attendance' list each week. I have names indicated by row, and dates indicated by column. I mark present with an 'x'. I would like to select each week (5 days so 5 columns) for a group of students (31 students, so 31 rows) and see if they showed up at least once that week. I do not want to count if they were there multiple days, but just know how many students out of the 31 showed up at least once that week. Is there a formula for this?

    1. Hi Kelsey,

      You can create additional columns right at the end of each week and use the formula like this for each student:
      =IF(COUNTA($B2:$F2)>0,"attended","absent")

      Please refer to these articles explaining how these functions work:
      IF function
      COUNTA function

  12. How to count the Quantity of Milk Chocolates ?

  13. Hello!
    I need to count "No" values of users on a column, but this count needs to be conditioned by this:
    If the "No" value is already on another row of the same column and belongs to the same user (this can be checked because I'm using one app ID on a specific column), then don't count it.
    How can I filter that with formulas?

    1. Hello Hebert,

      If I get it right, the quickest and easiest way is to use our Remove Duplicate Rows add-on to find all unique rows and mark them in a status column.
      Then use COUNTIFS to count those rows that have "No" as the answer and that are Unique:
      =COUNTIFS(B2:B10,"No",C2:C10,"Unique")

  14. Hi,

    I've been trying out the different formulas that seem to apply to my sheet, but it's weird, because the formula works, but the result is wrong. I have column F, which lists down the names of several people repeatedly.

    Once someone completes a task in the list, they are to change the cell's background color to Shamrock Green (hex code is #0F9D58).

    In a separate sheet for a summary, I have a column that serves to count the total number of tasks that they have completed (marked by the background color). I've been testing it out by coloring the cell accordingly, but the value that is returned is 0.

    =COUNTIFS(F:F,valuesByColor("#0F9D58","#000000",TASKLIST!F:F),F:F,"NAME")

    This formula, which I've learned about from your other article, works fine by itself! But I need to countif the name of the person when the cell has been colored Shamrock Green.

    =COUNTA(valuesByColor("#0F9D58","#000000",TASKLIST!F:F))

    Would appreciate any help! Thanks.

    1. Hi Pocholo,

      I'm happy to let you know that we released new versions of our Function by Color and Power Tools add-ons that include two new functions that can be used in COUNTIFS/SUMIFS in order to process cells based on colors & contents.

      You will find all the necessary info about these functions in this tutorial.

    2. Sorry, here's the formula that I've been trying to use:

      =COUNTIFS(TASKLIST!F:F,valuesByColor("#0F9D58","#000000",TASKLIST!F:F),TASKLIST!F:F,"NAME")

      1. Hi Pocholo,

        Unfortunately, since our valuesByColor returns values ​​from cells, it cannot be wrapped in another condition for another column. We'll consider improving the formula in the future if it's technically possible.

        For now, I can only offer to try and use Scripts. Here's an overview of Google Apps Script with a lot of helpful content and links:
        https://developers.google.com/apps-script/overview

        1. Hi Natalia,

          Got that! Appreciate the response -- just wanted to confirm as well, whether my formula was constructed correctly, or was it even supported by the function, since there is no error message and the cell just returns a 0 value.

          Thank you, and more power to your blog! They're quite informative and very helpful when troubleshooting and navigating the nuances between Google Sheets and Excel. :)

  15. I am copying some formulas from Excel over to Sheets and one of them just isn't translating.

    =SUM(COUNTIFS(A21:A93, {"*Contacted*","*Intro*","*Webinar*","*Disclosed*","*Application*","*Territory*"}, B21:B93, "*Business Professional*"))

    The answer should be 16

    But when entering the same formula into Google, it returns 4.

    I was trying to find out if the syntax is just different in Google Sheets but so far haven't figured it out.
    I'm guessing it's in the multi criteria between the "{" brackets... but... can't figure it out.

    Thanks

    1. Looks like all I needed was to add

      ArrayFormula(

      To the front of it

  16. Hi Guys..
    I hope you all doing fine…

    I have problem ..
    In google sheet,

    I want to sum (range of number) if the criteria is matches the formula below.
    I have multiple ranges and criteria .
    And one of the ranges I need to add 2 criteria .

    so for example :
    I need to calculate the salary for the employees
    If the employee (( city) (The range)) is from ((city 1 or city 2)(multiple criterias ))
    And other ranges also like ( showroom, age and profession etc) .

    Thank you in advance ..

  17. I try to find the number of cells with a value greater than 30 but it gives me an error. This is the formula I use. Do you know what I’m doing wrong?

    =countif(c259:c261,”>30”)

    Thanks

    1. Hello Ryan,

      Try using straight quotes instead of smart ones for the condition:
      =COUNTIF(C259:C261,">30")

  18. I'm using your formula, but changing the inputs. I'm using a QUERY instead of just a fixed range. For some reason, I have a number of values that result in an empty cell. If I remove the ,) at the end of the IF-formula, I have a number of cells that output FALSE.

    Is there a way to get rid of those?

    1. Hello Juul,

      For us to be able to suggest anything, please provide your formula so we could see what you enter exactly.

  19. Hi,
    I have a range of data that contains dollar amounts and I am trying to find any amount that is greater than or equal to $5 and color coordinate it based on how many times it appears in the data set. For example the first time a cell contains data greater than or equal to $5, it would be color coded as green, the second time it happens, it would be color coded as yellow, etc.

    Can you help with that?
    Thanks!

    1. Hi Nicole,

      You should put COUNTIF into the IF formula and use both in the conditional formatting.

      For example, (assuming your data starts in A2) this is the formula for conditional formatting that will colour the first occurrence of the value that is >=5 in green:
      =(IF(A1>=5,COUNTIF($A$2:A2,">=5"),""))=1

      To colour the second occurrence, create another rule with another colour and change 1 to 2:
      =(IF(A1>=5,COUNTIF($A$2:A2,">=5"),""))=2

      And so on.

      Hope this helps! :)

  20. I have a spreadsheet where i need a formula to count for a certain value in column B, but if that value is counted, it needs to reflect a number in column S. So if B4 meets the criteria, I need the value of H4 to appear.

    Thank you in advance for any help

    1. Excuse me, the H4 in the phrase "I need the value of H4 to appear" should be replaced with S4 to match my earlier example.

      Thank you.

      1. Another clarification, this spreadsheet is on google sheets.

        Thank you

  21. Hello,

    I used the formula =COUNTIF(M3, "*(Pme)*" to tally the number of times that a specific cell contains a coding tag. However, the formula is treating 0/1 as No/Yes instead of giving me a total. Can I adjust the formula to count the number of times the coding tag appears instead?

    For example, I have cell M3 which contains the sentence "Also, I (S) know (Pme) plenty of straight guys (Ph) who (S) are attracted (Pme) to more masculine women (Ph)". The formula above produced the number 1, but I want it to produce the number 2 because the tag (Pme) appears twice in this sentence.

    Any feedback would be appreciated. Thank you!

    1. Sorry, I forgot to close the parentheses. The formula is: =COUNTIF(M3, "*(Pme)*").

      1. Okay, so upon further searching, I found =COUNTA and =COUNTUNIQUE, which yielded the results I wanted, but now I'm wondering the difference between the two. Thank you!

        1. I take the previous comment back-- these functions did not necessarily fix the issue. Sorry for the multiple posts and thank you for your time!

          1. Hello Sarah,

            none of the functions you mentioned will help: they count cells that contain the word you need, not the number of words within those cells. For your task, you need to use the LEN function like this:
            =(LEN(M3)-LEN(SUBSTITUTE(M3,"(Pme)","")))/LEN("(Pme)")

            1. LEN(M3) - counts the number of all chars in a cell
            2. LEN(SUBSTITUTE(M3,"(Pme)","")) - counts the number of chars without the entry of interest
            3. then subtract one from another to find the difference in chars
            4. and lastly, divide it by the total number of chars

            1. That fixed my spreadsheet, thank you so much for your help!

  22. Hello,
    I'm trying to find a way to use countifs based on values being split out of comma separated values, but something isn't quite clicking.
    If i have:
    ID
    1
    2
    3
    and then
    SCORE | SELECTION
    0 | 1,3
    1 | 2,3

    and i want to find out the total number of times that ID 3 was selected and there was a score of 0. The result should be 1.
    I feel like i'm close with
    =COUNTIFS(SPLIT($G$11:$G$14,","),3,$F$11:$F$14,0)
    But i get told "Array arguments to COUNTIFS are of different size."

    1. Hello Dan,

      The SPLIT function won't work like this. It's supposed to separate values to multiple cells rather than extract records needed for calculations. Our tech specialist has created the following array formula that should help:
      =ArrayFormula(SUM((MID($G$12:$G$13,FIND(",",$G$12:$G$13)+1,1)="3")*(LEFT($G$12:$G$13, 1)="0")))

  23. Hello,
    i use Google Sheet and i would like to calculate how many cells there are in a column of the same color, provided there is a value from another colone. COUNTIF is not good for color

    example:
    (A2: A100; A15; B2: B11; "Acquired")

    Thanks for your help. =)

      1. OK, thanks.
        but that's not exactly what I'm trying to do.
        This is not a COUNTIF but rather a COUNTIFS that I would need.

        this example may be better explained.

        A B C D
        1 | color 1 | Acquired | Total color 1 Acquired | 2 |
        2 | color 1 | Blocked | Total color 2 Acquired | 1 |
        3 | color 2 | Blocked | Total color 1 Bloqued | 1 |
        4 | color 2 | Acquired | Total color 2 Bloqued | 1 |
        5 | color 1 | Acquired |

        1. Tony,

          Since there's no standard way of counting cells by their colours, our tool will still be useful.
          If I understand your task correctly and "color 1" and "color 2" are cells with backgrounds, you can generate a formula with our Function by colour tool and then use it in your COUNTIFS, like this:
          =COUNTIFS(A2:A6,valuesByColor("#d9ead3","#000000",Sheet10!A2:A6),B2:B6,"Acquired")

          The part in bold was generated by our add-on.
          The entire formula counts all cells in column B with a word Acquired if a cell in column A is coloured in accordance with a pattern in valuesByColor.

          1. OK, thanks.
            But I still have a little problem.
            I think there is a problem with the third parameter of valueByColor "srcRange", it returns 1 whereas it should send me back 9.
            I think that once he has found a good result, he does not continue the loop in the range.

            sorry for the double post -_-"

            1. I tried the method "valueByColor" without going through a COUNTIFS, and I realized that it sends me the values ​​in the cells, but what I want to do is count the number of cells of the same color provided that the cell is acquired (color in colone A, and acquired in colone B).

              1. ok, I just sent you the file with a copy of our conversation.

                Thanks.

              3. Tony,

                It's rather difficult to tell why your formula doesn't work. Could you please share your spreadsheets with us to have a look? It's support@4-bits.com. We don't monitor its Inbox, so please confirm by replying here once you share the file. Thank you.

  24. Hello -
    I used your tip to figure out how to count non blank cells in a column on a certain sheet tab (Tab1). This formula works for that: =COUNTIF('Tab1'!Y2:Y889, "")
    I wish to further filter the count by a text value in another column. I want to count the non blank cells in column Y that have the value "A" in column N. Then count the non blank cells in column Y that have the value "B" in column N. This formula doesn't work - I am not sure how to connect them. =COUNTIF('Tab1'!Y2:Y889, "") AND COUNTIF('Tab1'!N1:N930,"A") Thank you so much.

    1. Note the characters between the double quotes in the formulas above are these - not sure why they did not display. If they don't display again in this comment, they are the less than sign and the greater than sign. Thank you again.

      1. Hello Casey,

        You need to use COUNTIFS for the task:
        =COUNTIFS(Tab1!Y2:Y889,"<>",Tab1!N2:N889,"A")

        Please keep in mind that the function requires ranges of the same sizes. Based on your example, you'll have to enter either rows 2:889 for both columns or 1:930.

        1. Thank you, Natalia - I thought I might need to use IFS, but I had the syntax garbled. Thank you so much - this worked exactly as I wanted.

  25. We want to count the values between certain dates (for our monthly report).
    Our document is structured like this:
    A column: Date (eg. 01-01-2019)
    B column: Value (eg. Owned, earned or paid)
    What's the correct formula to count the number of Owned between 01-01-2019 and 31-01-2019? Any help would be greatly appreciated!

    1. Levi,

      Please try this formula:
      =COUNTIFS(B2:B10,"Owned",A2:A10,">="&DATE(2019,1,1),A2:A10,"<="&DATE(2019,1,31))

  26. I have 4 tabs of information with columns of information that are in drop-down lists. I need to calculate the total number of times each of the items in the drop-down lists is selected (on a 5th tab). I've created the 5th tab and done the "COUNTIF" but it's not totaling. Instead, there's an error.

    1. To clarify, this is on a Google Sheet.

      1. Bill,

        will you be able to share your file with us at support@4-bits.com? If so, please reply here once it's shared. We'll look at how your data is stored exactly and try to come up with a solution.
        Thank you.

  27. Hi,

    Can you help me in getting a formula for a countif with cells containing duplicate texts.
    Example:

    R1 test
    R2 test | test

    when i use this; =countif(A:A,"*test*"), i only get 2; but I want to get 3 as the result.

    Thanks for the help.

    1. Hi Ralph,

      If your duplicates are divided by the same "|" character, you can try this formula:
      =COUNTIF(ArrayFormula(SPLIT(JOIN(" | ",A:A)," | ")),"test")

      Otherwise, you can use our Split tool to divide cells with duplicates by their delimiters and then count all occurrences.

  28. I want to know the total value of column b if column f is John column h is July

  29. What if I need a pie chart to show payment

    If Chocolate Milk, Count the Total Value of all the orders.

    I have a data set where the 'Chocolate Milk' has different values, but I need the formula to calculate all the column A results if 'Cholocate Milk', and take the value from another Column. :-/

    1. Hello Jane,

      To find out the total only for those cells that contain "Chocolate Milk", you need to use the SUMIF function instead. You'll find the tutorial about it on this page.

  30. Dear Sir ,
    I am having a set of data having some duplicate values in it . I wants to mark its frequency in its adjacent column except first one .
    like
    1
    1 duplicate
    1 duplicate
    2
    3
    2 duplicate
    4
    5
    2 duplicate

    1. Hello Abhinav,

      I'm sorry, I'm afraid there's no easy formula to do that.

      You can either identify duplicates without the 1st occurrences using a formula like this:
      =IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "")

      or count duplicates by the example of this article for Excel.

  31. Hi,

    I'm trying to use a cell as the criterion for a countif search, but I can't make it work.

    Name Running Count
    Joe Smith (=countif(List1!F:F,"*Smith, Joe*")

    What I would like to do is be able to have sheets use a cell, so then I could copy the same formula all the way down. Something like =countif(List1!F:F,[text from cell a2]).

    Is this possible?

    1. Also, I forgot to say that the text from A2 that I will be searching for will be in a cell with other names as well, so I need a wildcard to be able to search withing the cells of names to find the one I want.

      So, the cell I'm searching might say "Peterson, John, Johnson, Mike, Thompson, Charles, Smith, Joe, Williams, Sam"

      From that I need to be able to search for the name I want, which would be found in cell A2.

      I hope that makes sense.

      1. Hi Jeff,

        If you have "Joe Smith" written in A2, but the range to search in contains names like "Smith, Joe", referring to A2 won't give you the correct result. The formula will look for the exact contents of A2.

        In order to use A2 as a source cell, you need to extract first and last names and concatenate them separating with a comma. Here's what I can offer to try:

        =COUNTIF(List1!F:F,"*"&CONCATENATE(RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),", ",LEFT(A2, SEARCH(" ",A2)-1))&"*")

  32. Hi, I am trying to find the count of TRUE Values and False Values. My data set is from B2:K146. When I do =COUNTIF(B1:K146,"TRUE")I get an incorrect number. When try the same formual with False, I get a HUGE number that isn't even the amount of cells I have filled with either True or False. Any help is appreciated, I have a huge data set full of True/False that I have to parse out and being able to use a formula to figure out how many are true vs false will be a lifesaver!!!!1

    1. Hi Nickole,

      You can try omitting double quotes in your formula since TRUE and FALSE are Booleans that doesn't necessarily require these:
      =COUNTIF(B2:K146,TRUE)

      Also, make sure to apply the correct data format to your resulting cell: Format > Number > Number.

  33. Hi, I have 3 columns
    Column C = Category
    Column D = boolean

    I have many cateogies
    I need to count the amount of rows that has a specific category with TRUE in the column D.
    I would need something like this: =countif(C:C, and("C=category1",D=true))

    Can you help me? THANKS!
    example: count how many CGAs has true should give: 2
    C__|D__
    CGA|true
    VGA|true
    CGA|false
    VGA|false
    CGA|true

    THANKS!!!

  34. Hello!
    I've tried to do COUNTIF for multiple variables, but it's not pulling the correct data.
    How do I set up the formula so that both variables in a row are true.
    Example data:
    Bob, Male, Form 1
    George, Male, Form 2
    Sally, Female, Form 1
    Jo, Female, Form 1
    Sue, Male, Form 1
    Jane, Female, Form 1
    I would want formula to output the numbers that correspond to gender:
    Form 1 - 2 Male
    Form 1 - 3 Female
    Form 2 - 1 Male

    1. Hi Melissa,

      I'm sorry, I don't quite understand your task. If you could create a sample spreadsheet with your source data and the result you'd like to get and share it to support@4-bits.com, I may be able to come up with a solution.
      Once you share the file, please let me know by replying here.

  35. how to filter data or counts the value if a cell contain more than one value which is seperated by commas

  36. You are a life saver, I feel into the trap of =COUNTIF(C6:C16, D6:D16,"Milk Chocolate") and this ironed that out.

  37. Is it possible to do a countif of cells based on criteria of a previous cell? For example, I would like to count the number of cells in a row with the number 26, but only if the previous cell in the row is the number 25.

  38. Hi! Is there a way to exclude qualifications? I have a column with multiple sources listed in each cell "Referral, Job Site, Agency, LinkedIn" and I'd like to only count the ones with the word "LinkedIn" in the cell AND the ones where LinkedIn appears in a cell with other sources too - but as separate counts.

  39. Thanks for the article! I have this range where returns values from another range of data based on criteria, I put iferror to return "" where there's no valid data, then I want to count the cells with valid data returned with =COUNTIF(E3:AI3,"""") but it keeps counting the whole range with "" as well. Can you help me? Thank you!

    1. Hi Tramy,

      I'm sorry I'm afraid I don't really understand the task. You said IFERROR returns "" for invalid data. But then you are trying to count valid data and indicate it as "" as well.
      Could you please describe the task in more detail and give the examples of valid/invalid data?

  40. I am doing a volunteer gig and they are pushing my limits of programming but I am learning so much. I need some help. I have two sheets.
    Rating Data: Forces a unique client ID number in column A which is also named range "PTSD_ClientID".
    L1 Data: column A, or named range "L1_ClientID" forces validation to enter a value found in column A of Rating Data. It does not enforce unique values.

    Here's what I would love some help with. I have a column I in the Rating Data sheet that I would like to display of the count of all entries for that row's client ID (also named range PTSD_ClientID" that is entered in L1 Data.

    Any recommendations or tutorials to send me to on how I might achieve this. Thanks in advance!

    1. Hi Erika,

      If I understand your task correctly, you need to use the formula like this on the Rating Data sheet:
      =COUNTIF(Sheet2!A:A,$A1)

      where Sheet2 is your L1 Data, A:A is column A on the L1 Data sheet, and A1 is a cell with the unique ID on Rating Data.

  41. Hello, can you help me with a Google sheet formula to count how many days a student attended tutoring, where:
    A1 Last Name
    B1 First Name
    C1 Date (of the week the student attended tutoring from Tuesday to Friday)

    The data contains 100 students where some attended 1 day, some 2+, some 3+, etc.

    Thank you very much.

    1. Hello Alejandra,
      If I get it right, you need to add up numbers entered next to the student names. For that, you need to use SUMIF rather than COUNTIF. We have an article about it as well, you'll find it here.

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

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

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

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

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

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

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

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

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

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