Google Spreadsheet COUNTIF function with formula examples

Google Sheets COUNTIF is one of the easiest functions to learn and one of the handiest to use.

It's time to pick up some knowledge on how COUNTIF is used in Google Spreadsheet and learn why this function makes a true Google Spreadsheet companion.

What is COUNTIF function in Google Sheets?

This short helper allows us to count how many times a certain value appears within a specified data range.

COUNTIF syntax in Google Sheets

The syntax of our function and its arguments are as follows:

=COUNTIF(range, criterion)
  • range - a range of cells where we want to count a certain value. Required.
  • criterion or searching criterion - a value to find and count across the data range indicated in the first argument. Required.

Google Spreadsheet COUNTIF in practice

It may seem that COUNTIF is so simple that it doesn't even count as a function (pun intended), but in truth its potential is quite impressive. Its searching criteria alone is enough to earn such a description.

The thing is that we can decide to look for not only concrete values but also those that meet certain criteria.

It's high time to try and build a formula together.

Google Spreadsheet COUNTIF for text and numbers (exact match)

Let's suppose your company sells various types of chocolate in several consumer regions and works with many clients.

This is how your sales data look like in Google Sheets:
Sales data in Google Sheets

Let's begin with the basics.

We need to count the number of "Milk Chocolate" sold. Place the cursor in the cell where you want to get the result and enter the equality sign (=). Google Sheets immediately understands that we are going to enter a formula. As soon as you type the letter "C", it will prompt you to choose a function that begins with this letter. Select "COUNTIF".
COUNTIF for text
The first argument of COUNTIF is represented by the following range: D6:D16. 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".

Also, don't forget to enter double quotes ("") when using text values.

Our final formula looks as follows:

=COUNTIF(D6:D16,"Milk Chocolate")

As a result, we get three sales of this type of chocolate.

Note. COUNTIF function works with a single cell or neighboring columns. In other words, you can't indicate a few separate cells or columns and rows. Please see the examples below.

Incorrect formulas:

=COUNTIF(C6:C16, D6:D16,"Milk Chocolate")

=COUNTIF(D6, D8, D10, D12, D14,"Milk Chocolate")

Correct usage:

=COUNTIF(C6:D16,"Milk Chocolate")

=COUNTIF(D6,"Milk Chocolate") + COUNTIF(D8,"Milk Chocolate") + COUNTIF(D10,"Milk Chocolate") + COUNTIF(D12,"Milk Chocolate") + COUNTIF(D14,"Milk Chocolate")

You may have noticed that it's not really convenient to set the searching criteria in the formula - you have to edit it every time. The better decision would be to write the criteria down other Google Sheets cell and reference that cell in the formula.

Let's count the number of occurred sales in the "West" region using the cell reference in COUNTIF. We'll get the following formula:

=COUNTIF(C6:C16,A3)

The function uses the content of A3 (the text value "West") in its calculations. As you can see, it's a lot easier now to edit the formula and its searching criteria.
Using cell reference in COUNTIF

Of course, we can do the same thing with numerical values. We can count the number of occurrences of the number "125" by indicating the number itself as a second argument:

=COUNTIF(E7:E17,125)

or by replacing it with a cell reference:

=COUNTIF(E7:E17,A3)

COUNTIF for numbers

Google Spreadsheet COUNTIF function and wildcard characters (partial match)

What is great about COUNTIF is that it can count whole cells as well as parts of the cell's contents. For that purpose, we use wildcard characters: "?", "*".

For instance, to count the sales in some particular region we can use only the part of its name: enter "?est" into B3. A question mark (?) replaces one character. We are going to look for the 4-letter words ending with "est", including spaces.

Use the following COUNTIF formula in B3:

=COUNTIF(C7:C17,A3)

As you already know, the formula can easily take the next form:

=COUNTIF(C7:C17, "?est")

And we can see 5 sales in the "West" region.

Now let us employ the B4 cell for another formula:

=COUNTIF(C7:C17,A4)

What is more, we'll change the criteria to "??st" in A4. It means that now we are going to look for 4-letter words ending with "st". Since in this case two regions ("West" and "East") satisfy our criteria, we will see nine sales:

COUNTIF partial match

Similarly, we can count the number of sales of the goods using an asterisk (*). This symbol replaces not just one, but any number of characters:

Asterisk and COUNTIF

"*Chocolate" criteria counts all the products ending with "Chocolate".

"Chocolate*" criteria counts all the products starting with "Chocolate".

And, as you may guess, if we enter "*Chocolate*", we're going to look for all the products that contain the word "Chocolate".

Note. If you need to count the number of words that contain an asterisk (*) and a question mark (?), then use tilde sign (~) before those characters. In this case, COUNTIF will treat them as simple signs rather than searching characters. For example, if we want to look for the values that contain "?", the formula will be:

=COUNTIF(D7:D15,"*~?*")

COUNTIF Google Sheets for less than, greater than or equal to

The COUNTIF function is able to count not only how many times some number appears, but also how many of the numbers are greater than/less than/equal to/not equal to another specified number.

For that purpose, we use corresponding mathematical operators: "=", ">", "<", ">=", "<=", "<>".

Check out the table below to see how it works:

Criteria Formula example Description
The number is greater than =COUNTIF(F9:F19,">100") Count cells where values are greater than 100.
The number is less than =COUNTIF(F9:F19,"<100") Count cells where values are less than 100.
The number equals to =COUNTIF(F9:F19,"=100") Count cells where values equal to 100.
The number is not equal to =COUNTIF(F9:F19,"<>100") Count cells where values are not equal to 100.
The number is greater than or equal to =COUNTIF(F9:F19,">=100") Count cells where values are greater than or equal to 100.
The number is less than or equal to =COUNTIF(F9:F19,"<=100") Count cells where values are less than or equal to 100.
Note. It's very important to enclose the mathematical operator along with a number in the double quotes.

If you want to change the criteria without altering the formula, you can reference the cells as well.

Let us reference A3 and put the formula in B3, just as we did before:

=COUNTIF(F9:F19,A3)

To create more sophisticated criteria, use an ampersand (&).

For example, B4 contains a formula which counts the number of values greater than or equal to 100 in the E9:E19 range:

=COUNTIF(E9:E19,">="&A4)

B5 has the very same criteria, but we reference not only the number in that cell but also a mathematical operator. This makes it even easier to adapt COUNTIF formula if necessary:

=COUNTIF(E9:E19,A6&A5)

COUNTIF variations

Google Spreadsheet COUNTIF with multiple criteria

Sometimes it's necessary to count the number of values that answer multiple criteria at once. For that purpose, we should use a few COUNTIF functions in a single cell at a time.

Let's count the number of sales of black and white chocolate. To do that, enter the following formula in B4:

=COUNTIF(D7:D17,"*Milk*") + COUNTIF(D7:D17,"*Dark*")

Please note that we use an asterisk (*) to ensure that the words "dark" and "milk" will be counted no matter where these words are in the cell - at the beginning, in the middle, or at the end.

Again, we can write the formula using cell references. You can see how it looks like on the screenshot below in B3. The result remains the same:
COUNTIF with multiple criteria

Remember, using multiple criteria, we can count how many times a certain number appears within a certain range.

We are going to count the number of total sales between 200 and 400:
Sales between 200 and 400

We take the number of totals under 400 and subtract the number of total sales under 200 using the next formula:

=C0UNTIF(F7:F17,"<=400") - COUNTIF(F7:F17,"<=200")

The formula returns the number of sales more than 200 but less than 400.

If we want to reference A3 and A4 that contain the criteria, the formula will be a bit simpler:

=COUNTIF(F7:F17, A4) - COUNTIF(F7:F17, A3)

A3 cell will have "<=200" criteria, while A4 - "<=400".

Put both formulas into B3 and B4 and make sure that the result doesn't change - 3 sales over the needed range.

COUNTIF Google Sheets for blank and non-blank cells

With the help of COUNTIF, we can also count the number of blank or non-blank cells within some range.

Let's suppose that we successfully sold the product and marked it as "Paid". If the customer declined the goods, we write zero (0) in the cell. If the deal wasn't closed, the cell remains empty.

To count non-blank cells with any value, use the following:

=COUNTIF(F7:F15,"<>")

or

=COUNTIF(F7:F15,A3)

To count the number of empty cells, make sure to put the COUNTIF formula in the following way:

=COUNTIF(F7:F15,"")

or

=COUNTIF(F7:F15,A4)

The number of cells with a textual value is counted like this:

=COUNTIF(F7:F15,"*")

or

=COUNTIF(F7:F15,A5)

Screenshot below shows that A3, A4, and A5 cells include our criteria:
How to count blank or non-blank cells

Thus, we can see 4 closed deals, 3 of which were paid for and 5 of which have no markings yet and, consequently, are not closed.

COUNTIF and conditional formatting

There is one interesting opportunity that Google Sheets offer - to change the cell's format (like its color) depending on some criteria. For example, we can highlight the values that appear more often in green.

COUNTIF function can play a small part here as well.

Select the range of the cells that you want to format in some special way. Click Format -> Conditional formatting...

In the Format cells if... drop-down list choose the last option Custom formula is, and enter the following formula into the appeared field:

=COUNTIF($B$10:$B$39,B10)/COUNTIF($B$10:$B$39,"*")>0.4

It means that the condition will be answered if the value from B10 appears within B10:B39 in more than 40% of cases:

Changing a formatting with COUNTIF

In a similar way, we add two more formatting rule criteria - if the cell value appears more often than in 25% of cases and more often than in 15%:

=COUNTIF($B$10:$B$39,B10)/COUNTIF($B$10:$B$39,"*")>0.25

=COUNTIF($B$10:$B$39,B10)/COUNTIF($B$10:$B$39,"*")>0.15

Keep in mind that the first criterion will be checked beforehand, and if it's met, the rest won't apply. That is why you'd better start with the most unique values moving to the most common ones. If the cell value doesn't meet any criteria, its format will remain intact.
Conditional format rules and COUNTIF

You can see that the colour of the cells has changed according to our criteria.

To make sure, we also counted the frequency of some values in C3:C6 using COUNTIF function. The results confirm that COUNTIF in formatting rule was applied correctly.

All these function examples give us a clear understanding of how Google Spreadsheet COUNTIF offers multiple opportunities to work with the data in a most efficient way.

You may also be interested in:

111 Responses to "Google Spreadsheet COUNTIF function with formula examples"

  1. loise says:

    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?

  2. Lisa says:

    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?

    • 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!

  3. Mary says:

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

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

  4. Patrice says:

    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.

    • Patrice says:

      [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

  5. Jess says:

    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?

  6. Rachel says:

    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!

  7. John says:

    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

  8. Martin says:

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

  9. JANHO says:

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

  10. Mike says:

    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.

  11. Ondra says:

    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

    • Rich says:

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

  12. Lyf says:

    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?

    • Doug says:

      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?

      • Lyf says:

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

      • Lyf says:

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

      • Lyf says:

        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!

  13. Sami says:

    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. Nicholas traynor-richardson says:

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

  15. Luke Douglas says:

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

  16. Rick Prescott says:

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

  17. Shelby says:

    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.

  18. Inx says:

    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

  19. Doug says:

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

  20. Ferenc says:

    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!

  21. Teco says:

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

  22. Marcus Smith says:

    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

    • Nick Mikhuta says:

      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.

  23. mate wierdl says:

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

  24. Loren says:

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

  25. Jeffrey S Smith says:

    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.

  26. Alejandra says:

    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.

  27. Erika Romenesko says:

    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!

  28. Tramy says:

    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!

    • 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?

  29. Katherine says:

    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.

  30. Andy says:

    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.

  31. Sean says:

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

  32. jack says:

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

  33. Melissa says:

    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

  34. Luciano says:

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

  35. Nickole says:

    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

  36. Jeff says:

    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?

    • Jeff says:

      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.

      • 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))&"*")

  37. Abhinav says:

    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

  38. Jane says:

    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. :-/

  39. Teri says:

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

  40. Ralph says:

    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.

  41. BILL G says:

    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.

  42. Levi says:

    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!

  43. Casey says:

    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.

  44. Tony says:

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

  45. Dan says:

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

    • 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")))

  46. Sara says:

    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!

    • Sara says:

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

      • Sara says:

        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!

        • Sara says:

          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!

          • 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
  47. Parker Lewis says:

    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

  48. Nicole says:

    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!

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

  49. Juul Kabas says:

    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?

  50. Ryan Smith says:

    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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!