Search
Close this search box.

An Easy Google Sheets Count Cells with Text Guide for 2024

Fact Checked By Cindy Wong

How do you use the Google Sheets count cells with text function? The easiest way to count cells with text is using either the COUNTA or COUNTIF functions. However, note that the COUNTIF function only counts cells with text, while the COUNTA function will count text and numbers.

In today’s Google Sheets count cells with text guide, I’ll show you how to count cells with my step-by-step instructions and screenshots. Read on to learn more.

Google Sheets Count Cells with Text Using COUNTA

COUNTA means to count all. The function lets you determine the range of cells you want to count, including letters and numbers.

Follow my step-by-step guide to learn Google Sheets count cells with text using the COUNTA function:

  1. Select the cell where you will put your formula.
  2. Type in the formula: =COUNTA(
Google Sheets count cells with text—example using the =COUNTA( formula.
  1. Select the range you want to count.
Selected range to count example.
  1. This will return the number of cells with any text in them.
This will return the number of cells with any text in them.

The COUNTA function is simplest when you want to count a cell that contains text in Google Sheets.

Google Sheets Count Cells With Text Using COUNTIF

Now, I will take you through a few examples of Google Sheets count cells with text using the COUNTIF function.

Let’s first look at the syntax for the function to make it a little easier for you to understand the examples.

The syntax for the COUNTIF function is written as follows:

=COUNTIF(range, criterion)

Using this function, Google Sheets will count cells that contain a value in the range, providing it meets the criterion.

  • The range indicates the number of cells with text in Google Sheets.
  • The criterion specifies which cells with the text you want to count.

Google Sheets Count Cell Text: Using a Wildcard

The COUNTIF Google Sheets formula counts cells with specific text by inputting the specified texts as the criterion. If you use the * wildcard, it will count cells with any text but not numbers.

COUNTIF Function with *wildcard example.
=COUNTIF(A1:C10,”*”)

Here are the steps I used in the GIF above:

  1. Click an empty cell and type: =COUNTIF(
  2. Highlight the data range you want to search.
  3. Add a comma, then “*”.
  4. Press “Enter

You can see that COUNTA and COUNTIF return different results because using the above method does not count cells with numbers; it only counts text.

Different COUNTA and COUNTIF return results

Google Sheets Count Cells With Text: Specific Values

The simplest method for counting specific texts in Google Sheets uses the COUNTIF function, which counts cells based on a specified condition. Here’s the syntax for counting specific text:

=COUNTIF(range, "text")

Replace “range” with the cell range you want to search and “text” with the specific text you want to count.

How to count specific words in Google Sheets

Here’s how to count specific words in Google Sheets:

  1. Select the cell where you will put your formula.
  2. Type in the formula: =COUNTIF(
  3. Select the range you want to count.
  4. Add a comma and type the part of the string we want to count in quotation marks. In our case, we used “EXAMPLE.”
  5. Press “Enter.”

How To Count Names in Google Sheets

Suppose you have the following sample data set and want to quickly count the number of times the name ‘Mark’ occurs in column A.

Example spreadsheet

Use the formula below to do this:

=COUNTIF(A2:A13,"Mark")

Here is a step-by-step guide on how to count cells with a specific text in Google Sheets using the COUNTIF Function:

  1. Select the cell where you will put your formula.
  2. Type in the formula: =COUNTIF(
  3. Select the range you want to count.
  4. Add a comma and type the word we want to count in quotation marks (“Mark” in the example above).
  5. Press “Enter.”

This will return the number of cells with the specific text in them.

COUNTIF formula to count cells with specific text in Google Sheets

Here is how the above COUNTIF formula works:

  • The first argument of this formula is the range where you have the data. In this example, it’s A2:A13 with the names.
  • The second argument is the criteria. This is used to check the value in the cell, and if this criterion is met, then the cell is counted.
    • In this example, I have manually entered the name Mark in double quotes.
    • If you have this criterion in a cell, you can use the reference here.

The above formula does a conditional count by going through all the cells in the range and counting those cells where the criterion text is present.

Note: The criterion text in this formula is not case-sensitive. So the result will be the same whether you use MARK, Mark, or mark.

In this example, I  used a name, but it can also be applied to other types of text, such as IDs or numbers.

How To Count Cells That Do Not Contain Specific Text

You can modify the COUNTIF function in Google Spreadsheets to count the number of cells with text that excludes a specific text. The following method specifically excludes a specific string of text.

In my example, if I have the dataset shown below and I want to count all the cells where the name is not Mark, I can easily use the COUNTIF function.

Below is the formula that will give the count of all the cells where the name is not Mark:

=COUNTIF(A2:A13,"<>Mark")
COUNTIF formula to count cells that does not contain a specific text

To learn how I did this, follow my step-by-step guide below:

  1. Select the cell where you will put your formula.
  2. Type in the formula: =COUNTIF(
  3. Select the range you want to count.
  4. Add a comma and type <> and the word you want to omit from the count.
    • In our example, that’s Mark.
  5. Press “Enter.”
    • This will return the number of cells without the specific text in them.

The above formula uses the range of the cells as the first argument and the criteria used is “<>Mark”. Here, the not-equal-to sign also needs to be within the double quotes.

Video Guide

Here’s my video guide on counting cells that contain specific information. If you prefer to learn with a real-world example, this is a good resource.

How To Count Cells that Contain Part of a String

In the examples above, we let Google Sheets count the number of cells with text for the entire cell content.

If we want to count if a cell contains the partial text we specified, in this case, the name Mark, we could use Mark inside the * wildcard like this: “*mark*”.

Below is the formula that will give you the count of all the cells with the word ‘mark’.

=COUNTIF(A2:A13,"*mark*")

Here’s how:

  1. Select the cell where you will put your formula.
  2. Type in the formula: =COUNTIF(
  3. Select the range you want to count.
  4. Add a comma and type the word we want to count in quotation marks. Enclose it in asterisks (*)
  5. Press “Enter.”
    • This will return the number of cells with any text in them.
Count Cells where there is a partial match

In the above function, the criterion is flanked by an asterisk symbol (*) on both sides.

The asterisk sign (*) is a wildcard character that can represent any number of characters in a formula. This means that when the formula checks for the given condition, there can be any number of characters/words before and after the criteria.

If the word “Mark” (or whatever your criterion is) is present in the cell, this formula would count the cell.

Caution: When using the asterisk sign (*) to count if a cell contains text in Google Sheets, it will consider the condition met as soon as the criterion text is found.

For example, if the cell contains similar text, such as “Market” or “Marketing,” these will be counted as these also contain the prefix ‘Mark,’ the criteria in the function.

If you want to use the function for counting text in Google Sheets by checking for multiple criteria, you must use the COUNTIFS function.

One question I am often asked is whether you can use the COUNTIF function to count the cells with colors. Unfortunately, you can not do this, as it requires some app script.

However, if you need more guidance on this, please refer to my tutorial on filtering and counting cells based on color.

Google Sheets Count Cells with Text Using SUMPRODUCT

You can also use SUMPRODUCT to count cells with text in Google Sheets. This function is a useful substitute for the COUNTA function when you only want to count text.

However, to use SUMPRODUCT to count cells with text, you need to combine it with the LEN function. The formula is pretty straightforward, and you only need to input the range like so:

 =SUMPRODUCT(--(LEN(range)>0))

Let’s look at an example to help you understand better.

  1. Click on an empty cell and input the function: =SUMPRODUCT(
Click on an empty cell and input the function =SUMPRODUCT(
  1. Type “LEN” after the opening bracket and select it from the formula suggestions.
Type LEN from the formula suggestions.
  1. Select the range you want to count.
Selected range to count.
  1. Close the bracket and add >0.
Close the bracket and add >0

This formula will return the number of cells with text.

This formula will return the number of cells with text.

Frequently Asked Questions

How Do I Count a Cell Containing Text in Google Sheets?

To count a cell containing text in Google Sheets, you can use the COUNTIF function. Here’s how to do it:

  1. Select the cell where you want to display the count.
  2. Type the formula into the cell: =COUNTA(
  3. Select the range of cells you want to count. For example, if you want to count all the cells in column A, you would use A:A as the range.
  4. Press “Enter.”

How Do I Count Cells with Text in a Row in Google Sheets?

To count a cell with text in Google Sheets, you can use the COUNTIF function by following the steps below:

  1. Select the cell where you want to display the count.
  2. Type the formula into the cell: =COUNTA(
  3. Select the row of cells you want to count.
  4. Press “Enter.”

How Do I Count the Number of Cells with Specific Text?

To count a cell with specific text in Google Sheets, you can use the COUNTIF function. Here’s how to do it:

  1. Select the cell where you want to display the count.
  2. Type the formula into the cell: =COUNTIF(
  3. Select the cell range you want to count.
  4. Add a comma and type the specific text in quotation marks.
  5. Press “Enter.”

Note that you don’t need to use a closing bracket in step two. When you press enter, Google Sheets will automatically recognize that you’re working on a formula and add the bracket for you.

How Do I Count Cells with the Same Value in Google Sheets?

To count a cell with the same value in Google Sheets, use the COUNTIF function and the following steps:

  1. Select the cell where you want to display the count.
  2. Type the formula into the cell: =COUNTA(
  3. Select the range of cells you want to count. For example, if you want to count all the cells in column A, you would use A:A as the range.
  4. Press “Enter.”

Can I Specify Multiple Criteria When Counting Cells?

To count cells with multiple criteria in Google Sheets or Excel, use the COUNTIFS function. This uses the same process as the COUNT function, but it allows you to add multiple conditions to your formula.

IFS functions also allow you to choose cells that contain additional names, numbers, or characters.

Google Sheets Count Cells With Text: Wrapping Up

Google Sheets counting cells with text is easy once you know what you’re doing. In this case, you can use the COUNTA function to count cells with any text or the COUNTIF formula to count cells with specific text.

I hope you found this Google Sheets count cells with text guide helpful. If you still need help, please check out our comprehensive Google Sheets and Forms course.

Which method did you like best? Let us know in the comments.

Related:

Most Popular Posts

10 thoughts on “An Easy Google Sheets Count Cells with Text Guide for 2024”

  1. Hi!,

    I have a question related with the above as I have a data like below and want to find the unique count. Can you pls help me out to get this done? Pls see the data below. I want to get the unique value of students who have eaten Apple OR Grapes. What would be the formula?

    Students Fruits
    A Apple
    B Apple + Grapes
    C Orange
    D Banana
    E Apple + Grapes
    F Grapes

    Narendra

    Reply
  2. Splendid and perfect instructions. Thanks a 1,000! And greetings from Niterói, Rio de Janeiro, Brazil ❤️️ 👍 🖖

    Reply
    • What if I want to count the number of times Mark appeared in a cell? Say example cell a1 contains mark / matt / alex / mark / jonathan / mark – this would ideally say 3 counts of mark, but using your formula, it would only count as 1 as they are in the same cell.

      Reply
  3. Thank you, this is useful. What if instead of expliciting the text inside the second argument I want to refer to a cell?

    e.g. in C3 I have “Salad” and I want to count all cells in that range containing *C3* that I know migh change.

    It doesn’t work with the cell.

    Reply
  4. Helpful, but I’m looking for some other scenario like if I have a list of cities in the 1st column and the 2nd one contains multiple reasons.
    Now I want to calculate all the data (from multiple sheets) on the main sheet containing Cities in column A and Reasons in columns B, C, D, E & F.

    Reply
  5. Hey Sumit, thanks for this post. What if instead of counting a simple text value I want to count how many occurrences of a text that is contained in a cell?

    I tried =COUNTIF(A:A,”*A2*”) but it doesn’t work, Thank you

    Reply
  6. I am curious if it is possible to make the reference in the last part in the formula for searching for a part of the cell value (usings the wild cards) could be relative.

    For example: i have a number of cells which I want the formula to check for:
    B2: V C2: M D2: A E2: w

    So I would the formula to check for the value in cell E2:
    =COUNTIF(A2:A13,”*mark*”) would then become: =COUNTIF(A2:A13,E2)

    But obviously, that doesn’t work as the wildcard/asterix is not present. So I made this:
    =COUNTIF(A2:A13,””*””&E2&””*””) but that also does not work.
    I tried to use the textjoin formula. But that also doesn’t seem to work.

    Is it possible to use a relative cell reference combined with the asterix/wildcards to search for a specific string in a part of the matrix/cells?

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!