An Easy Google Sheets Count Cells with Text Guide for 2024

To count cells with text, you’ll need to use a specific function. This can be done with either COUNTA or COUNTIF. However, note that the COUNTIF function only counts cells with text, while the COUNTA function will count text and numbers. These are both helpful features. And my guide below shows how to count cells with step-by-step instructions and screenshots.

How to Count Cells with Text Using the COUNTA Function

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(

3. Select the range you want to count.

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

How to Count Cells with Text in Google Sheets with 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(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.

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.

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.

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.

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

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: Counting Cells that Contain Text in a Spreadsheet

Here’s my video guide on counting cells that contain specific information. Note that this video guide shows both of the functions I described above. It’s a valuable resource if you prefer to learn with a real-world example.

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.

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(

2. Type “LEN” after the opening bracket and select it from the formula suggestions.

3. Select the range you want to count.

4. Close the bracket and add >0.

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:

• How to Get the Word Count in Google Sheets
• How To Remove Duplicates In Google Sheets
• Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
• Remove the First Character from a String in Google Sheets
• Count the Number of Characters in a Cell in Google Sheets
• IFS Function in Google Sheet
• Count Cells based on the Cell Color in Google Sheets