How to Count Cells With Text in Google Sheets

While working with Google Sheets, you may need to extract the cell count for a specific text, such as names, locations, products, etc. Counting these cells manually can be very time-consuming, especially when dealing with large datasets.

With the COUNT function in Google Sheets, you can count the number of cells that contain text.

The COUNTA function allows users to count the total number of cells with any data, whereas the COUNTIF function allows users to count the number of cells that meet certain criteria.

Using the COUNTA Function

A basic way to count cells containing text data is by using the COUNTA function. It gives you the total number of values within a given range. 

Here’s how you can use the COUNTA function in Google Sheets to find out the number of cells containing text data. 

1. Open Google Sheets.
2. Click the cell where you want your data to appear.
3. Type =COUNTA()
4. Insert Range into the formula. (Example: A2:A10)
5. Press Enter to view your results.
   

Using COUNTIF Function

If you have both text and numerical data in your datasheet, you can use the COUNTIF function with an asterisk sign to know how many cells contain data with text. 

Here is how you can do it:

1. Open Google Sheets and click where you want your results to appear. 
2. Type =COUNTIF()
3. Insert the range and add an asterisk sign after the range. (Example: H1:H11, “*”)
4. Press Enter, and your result will be displayed.
   

Keep in mind that a cell has to be completely empty for this function to count it as a blank cell. If your blank cells contain spaces, then they will be counted as a data cell.   

In such cases, you can use the asterisk sign with greater than operator to ignore blank cells with spaces. 

1. Click on an empty cell to display your result. 
2. Input the function =COUNTIF(A2:A10,”>0”&”*”). Select the proper range for your dataset.
3. Click Enter to View the results.
   

Count Cells with Specific Text

The COUNTIF function lets you filter results based on certain criteria, like counting cells that contain a specific word filter set by the user. 

Here is how you can use the COUNTIF Function to count cells containing specific text:

1. Open Google Sheets and click on the cell where you want the filtered data to appear. 
2. Type =COUNTIF()
3. Insert Range and filter into the formula. (Example: A2:A10, “John”)
4. Press Enter to view your results.
   

Count Cells That Contain Specific Text Anywhere

If a data cell contains data with more than one word and you are only looking to count a specific word in the data, you can use the COUNTIF function with a wildcard. Using an “Asterisk” sign before and after your filter text will count the cells regardless of the location of your filter. 

Here is how you can use this syntax in your datasheet.

1. Open Google Sheets and click on an empty cell where you want your result to appear.
2. Type =COUNTIF()
3. Insert the Range and Filter into the formula with a wildcard. (Example: A1:A10, “*John*”) and press Enter.
   

Count Cells That Do Not Contain Specific Data

You can also use the COUNTIF function with a not equal to operator in Google Sheets to count cells that do not contain a specific text. 

Here is how you can count cells that do not contain your specified data:

1. Open Google Sheets and click on an empty cell where you want your result to appear. 
2. Type =COUNTIF()
3. Insert Range and add not equal to operator with the filter. (Example: A2:A10, “<>John”)
4. Press Enter to view your results.
   

Count Cells With Two or More Texts

If you want to determine the number of times two or more texts appear in a range of cells, you can use the combination of COUNTIF with ARRAYFORMULA and SUM. 

For this example, we are going to count the number of two cities from a dataset.

The formula will be,

=ARRAYFORMULA(SUM(COUNTIFA17:A28,{“Miami”,“LA”})))