3 Easy Ways for Google Sheets to Count Cells with Text

When you’re working with large datasets in Google Sheets, you will sometimes need to count cells with text in Google Sheets. This could be names, ids, or even dates.

But can Google Sheets count cells with text?

Luckily like most other spreadsheets, you can count text in Google Sheets.

And thanks to awesome formulas in Google Sheets, this can be done in seconds. The count functions in Google spreadsheet count cells with text.

In this tutorial, I will show you a couple of scenarios where you can have google sheets count cells with text.

How to Count Cells Containing Text in Google Sheets

Here’s how to count cells containing text in Google Sheets::

1. Select the cell where you want to display the count.
2. Type the following 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.

Using the COUNTA Function

You can also use the COUNTA Google Sheets formula to count cells with text. The COUNTA function counts the numeric values for the cells in the range that are not empty.

This includes cells with error values and empty texts, but it does not count any blank cells.

The syntax for the COUNTA function is:

COUNTA(value1, [value2], ...)

Value1  (Required):  Represents the values that you want to count.

Value2  (Optional): Represents additional values that you want to count, with a limit of 255 arguments.

To make the COUNTA function count cells with texts only instead of all the non-blank cells, you need to put in the arguments in the count function.

Here is a step-by-step guide on how to count cells with texts in Google Sheets 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.

COUNTA vs COUNTIF vs LEN

The COUNTA function lets Google Sheets is like the “COUNTIF cell contains text” function, but it can also be any other value. To let Google Sheets count cells with specific text, you need to use COUNTIF functions instead. To count the number of characters in a cell, you will need to use the LEN function instead.

Using the COUNTIF Function

The COUNTIF Google Sheets formula counts cells with specific text by inputting the specified texts as the criterion.

Using the COUNTIF function for Google Sheets to count cells with text is a simple process.

COUNTIF Syntax

The syntax for the COUNTIF function is written as follows:

=COUNTIF(range, criterion)

Using this function, Google Sheets will count if contains a value in the range that meets the criterion.

• The range, in this case, specifies to Google Sheets to count the number of cells with text
• The criterion specifies which texts you want to be counted.

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

Using SUMPRODUCT Function

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 and not space characters.

However, to use SUMPRODUCT to count cells with text, you’ll need to combine it with the LEN function.

SUMPRODUCT Syntax

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

The formula itself is pretty straight forward, and all you need to do is input the range.

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.

Can Google Sheets Count Cells With Text With Specific Values?

Suppose you have a dataset, as shown below, and you want to quickly count the number of times the name ‘Mark’ occurs in column A.

Below is the formula you can use 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.
5. Click Enter.

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

Here is how this 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 which has the names
• The second argument is the criteria. This is what 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 that the criterion text used in this formula is not case-sensitive. So whether you use MARK, Mark, or mark, the result would be the same.

In this example, I have used a name, but it could be any text – such as ids or numbers.

How to Count Cells That Do Not Contain a Specific Text

You can modify the COUNTIF function in Google spreadsheet count number of cells with text without using a specific text.

For example, if I have the dataset as shown below and I want to count all the cells where the name is not Mark, I can easily do this with 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")

Here is a step-by-step guide on how to count cells without 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 <> and the word you want to omit from the count, in our example, that’s Mark.
5. Click 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 needs to be within the double quotes as well.

How to Count Cells with One of Specific Texts

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

So if we wanted to Google Sheets count if cell contains text specified, in this case the name Mark, we used “Mark” as the criterion. This counted all the cells where the entire cell content was ‘Mark’.

But let’s say, you have a dataset of full names (or some other text) along with the name and you want to count all the cells that contain the name ‘Mark’, then you can use the above formula.

In this case, you need to use the COUNTIF function with wild card characters.

Below is the formula that will give you the count of all the cells that have the word ‘Mark’ in it.

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

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

In simple terms, if the word Mark (or whatever your criterion is) is present in the cell, this formula would count the cell.

Caution: One thing you need to remember when using the asterisk sign (*) to count if cell contains text in Google Sheets is that it would consider the condition met as soon as the criterion text is found. For example, in the above example, in case the cell contains the text Market or Marketing, these would be counted as these also contain the text ‘Mark’ – which is out criteria in the function.

In case you want to use the function for counting text in Google Sheets by checking for multiple criteria, you need to use the COUNTIFS function.

One of the questions I often get is whether you can use the COUNTIF function to count the cells with colors. Unfortunately, you can not. However, here is a tutorial where I show how you can easily filter and count cells based on color.

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:

5. Select the cell where you want to display the count.
6. Type the following formula into the cell: =COUNTA(
7. 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.
8. Press Enter.

How Do I Count Cells with Text in a Row 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 following 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 that Contain Specific Text?

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 following formula into the cell: =COUNTIF(
3. Select the range of cells you want to count.
4. Add a comma and type the specific text in quotation marks.
5. Press Enter.

How Do I Count Cells with the Same Value 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 following 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.

Conclusion

Google Sheets counting cells with text is an easy topic to tackle once you know what you’re doing. In this case, you can use count functions like the COUNTA function if you want to count cells with any text and the COUNTIF Google Sheets formula to count cells with specific text.

Hopefully, you were able to follow this guide on how to count cells with text in Google Sheets and we’ve answered the question, “Can Google Sheets count cells with text”. If you still need help, please check out our comprehensive Google Sheets and Forms course.

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