How to count cells with text in Google Sheets

When you’re working on your Google Sheet, you might sometimes have large amounts of data. Some of the data might be missing or mixed with numbers. You may want to count the number of cells with data, a text, or even a specific text. Here’s how you can do that. 

3 methods to count cells with text in Google Sheets

  • Using the COUNTIF function
  • Using the COUNTA function
  • Using the SUMPRODUCT function

Method 1: Using COUNTIF 

COUNTIF essentially counts the cells if it passes a specific condition. 

Step 1: Identify the column you would like to count

Identify the column you want to count. You will pass the range as a parameter to the COUNTIF function. 

For instance, let’s take the B column that contains a combination of text strings, blank cells, and numeric values. Let’s count the number of cells that contain a text string using the COUNTIF function. 

Step 2: Pick a cell to display the count

Choose a cell where you want the output. Enter the COUNTIF function into this cell. 

For instance, say you choose cell D3 to display the output. 

Step 3: Enter the formula.

Enter the function name starting with “=”. You can manually type the function’s name, or pick COUNTIF from Google Sheets’ suggestions. 

You will now be able to see the syntax of the COUNTIF function. The function has two parameters. 

  • Range – The range of the cells you want to count
  • Criterion – The criteria to be passed for the cell to be counted

For instance, in this example the cell range I’d like to use is B1:B7, and the criterion is cells with text strings. To screen for text strings, we use the asterisk (*) symbol. The asterisk is called a wildcard character that will pick any text string in the range of cells you’ve entered. The formula that you’d use in this case, therefore, would be =COUNTIF(B1:B7, “*”).

Step 4: Press Enter to see the count

Press Enter after you’ve filled the parameters into the COUNTIF function. Cell D3 will now display the total number of cells containing a text string.

If you’d like a specific text string, just replace the asterisk with the desired text string. For instance, if you wanted to count cells containing the text string “Penguin”, we’d change the text string accordingly. Notice how the output changes to 2.


Method 2: Using COUNTA

This method ignores blank cells but includes cells containing numeric values. If you have numbers in your data and wish to exclude those cells counting, use the previous method. 

Step 1: Identify the range of cells and the cell to display the count

Let’s use the data from our previous example. Just like the previous method, identify the range of cells that you would like to count. Choose a cell to display the count. 

Step 2: Enter the formula

In the cell to display the count, start entering the function name. 

Pick COUNTA in the suggestions to see the syntax. 

Unlike the COUNTIF function, the COUNTA function can take up to 30 parameters. The first parameter is the range of cells. Subsequent parameters are the other cells to be considered if needed.

If you want just a plain vanilla computation, enter the formula =COUNTA(B1:B7). You’ll also see the answer in a popover in real-time. 

Step 3: Press Enter to see the count

After entering the formula, press Enter to see the count. You can see that it has excluded the missing cells but included cells with a number. 


Method 3: Using the SUMPRODUCT function

Sometimes, the COUNTA function can be sneaky. The cells sometimes appear to be blank, but they might have a space that means the cell is not empty. So, that cell may be included in the calculation by the COUNTA function. To avoid this, you can use the SUMPRODUCT function. 

Step 1: Identify the range of cells and the cell to display the count

Let’s use the data from our previous example. Just like the previous method, identify the range of cells that you’d like to count. Choose a cell to display the count. 

Step 2: Enter the formula

Start typing in the formula. You’ll see the syntax of the function appear, like so: 

The formula is =SUMPRODUCT(–(LEN(range)>0)), where range represents the range of cells. In this case, it’s B1 to B6. So, the formula would become =SUMPRODUCT(–(LEN(B1:B7)>0)).

Step 3: Press Enter to see the count

After entering the formula, press Enter to see the count. You’ll see that it has excluded the missing cells but included cells with a number. 


Wrapping up

No more struggling to count the cells with text in Google Sheets. Now, you know how to quickly count cells with data, text, or even specific text strings. 

Leave a Comment