Skip to Content

How to Count Cells with Text in Google Sheets: 2 Easiest Methods

Posted on Last updated:

Sometimes, you may have a spreadsheet that contains lots of text in the cells.

Counting those cells can surely be time-consuming and not an effective way of analyzing data!

Here, Google Sheets has an easy solution for you. It allows you to figure out and separate the number of cells that contain text strings in a few seconds.

Let us discuss using the COUNTA, SUMPRODUCT, and COUNTIF functions to find the cells containing the text string in Google Sheets.

This is a quick guide with lots of useful information. Make sure to read it till the end.

Click on the following links to jump to that particular section.

Download the Example Google Sheets

To practice Google Sheet features and functions discussed in the following sections, consider downloading the following Google Sheet. It includes spreadsheets and an example dataset used to discuss counting cells that contain text.

Click Here!

If you have your own Google Sheet ready with the required data, then skip downloading the above file.

How to count cells with text in Google Sheets

Google Sheets is one of the best spreadsheet programs out there. It boasts hundreds of functions to perform various calculations and tasks.

To count cells that contain text, you can use the COUNTA function.

Though there are other functions, including the COUNTIF and SUMPRODUCT, the COUNTA is the quickest way to count cells that contain any text or portion of text.

In other words, the COUNTA function is popular for counting the blank cells.

Explained: The COUNTA Function in Google Sheets

The COUNTA function is used to count the cells that hold letters and numbers. It is a simple array formula in Google Sheets with the following syntax.

=COUNTA(value1, [value2, …])

The “value” argument in the above formula represents the cells you wish to tally. You need to replace this argument with a cell reference holding the values.

Unlike other Count functions, the COUNTA is capable of counting text strings that contain one of the following values:

  • Textual data
  • Numbers
  • Formulas
  • Logical expressions
  • Dates
  • Errors, e.g. #DIV/0!
  • Cells without any data but holding a leading apostrophe (‘)
  • Cells holding an empty string (=” “)

Only those cells that are completely blank are ignored by this function.

Now, let us learn to use this function.

Task: Count the number of employees who received a bonus in December 2022.

Here are the steps to count cells that contain text.

  • Click on the cell “G2”
    (It is the cell where we wish to display the count of employees who received the bonus in 2023. You can choose to count the blank cell at the end of the column and so)
  • Type “=counta”
  • Select the first option from the popup or press the “Tab” key
  • Select the cell reference from D2 to D21 as “C2:C21”
    (Note that the bonus information is mentioned in the column D)
  • Complete the bracket using “)” on your keyboard
  • Press “Enter”

Here’s how our final formula looks,

=COUNTA(C2:C21)

Google Sheets will instantly display that 13 employees had received the bonus in December 2023.

Note that in the above example, our column (Bonus) contained the numerical values only.

In your case, the column may contain textual values or a mix of textual and numerical; the COUNTA formula should work without any errors, as above.

Q. What is another way to access the COUNTA function in Google Sheets?

You can choose from first selecting the cell and then typing or inserting the formula using the main menu.

Here are the steps to inserting the formula using the main menu,

  • Go to the main menu
  • Click on “Insert” tab
  • Select the “Function” from the popup
  • Choose “Statistical”
  • You will see a list of functions, scroll down and select the “COUNTA” function

This is a pretty long process when compared with selecting the cell and typing the formula.

If you are in a hurry, we suggest using the type method to insert the formula into the cell.

Q. What is the difference between the COUNT and COUNTA functions in Google Sheets?

The COUNT and COUNTA functions have similar working. The way in which each function treats the content of the cell separates them.

The COUNT function deals with numerical values only.

On the other hand, the COUNTA functions count values, including numbers, letters, formulas, logical expressions, and more.

Here is an example,

We have a total of 10 employees to whom we have paid an annual bonus in December 2022.

Out of those 10 people, only 7 could get the bonus, and 3 people didn’t receive any bonus. Those 3 people are mentioned with the keyword “NO BONUS”, as shown in the above image.

After using the COUNT and COUNTA functions, the results are as follows,

The COUNT function has counted all the numerical values from the “Bonus %” column, whereas the cells containing the “NO BONUS” as the value have been excluded.

The COUNTA function has counted all the values containing numbers and letters. So, as we don’t have any blank cells in the “Bonus %” column, the COUNTA function has displayed a count of 10.

How to count cell that contains specific text string in Google Sheets

In the above section, we learned to count the cells that contain values, which can be anything from numbers to textual formulas.

Now let us discuss using the Google Sheet to find cells that contain specific text.

We will use the COUNTIF function for this purpose. So, let us quickly understand the COUNTIF function syntax and working in Google Sheets.

Explained: The COUNTIF Function

The COUNTIF is a simple function in Google Sheets. It is used to count the cells containing the values that meet the specified criteria.

As from the name itself, this function is a combination of the COUNT and IF functions in Google Sheets. Simply put, you can count the number of times a specific condition has been met.

Here’s the general syntax of the COUNTIF function in Google Sheets,

=COUNTIF(range, criterion)

Let’s understand the COUNTIF function arguments.

  • “range” – It represents the cell range that you wish to test against certain conditions or criteria. You need to provide the cell reference for this argument
  • “criterion”: You need to replace this argument with the condition that will be tested against the cell values. In this article, as we wish to find a specific text string, we need to type the text string and make sure to use double quotation marks. Note that you can also create complex formulas using less than (<), greater than (>), and not equal to (<>) signs

As mentioned above, for the sake of this article, our focus is on finding text strings within multiple cells. So, make sure to use the double quotation marks while entering the text string within the cell. Otherwise, the formula will return an error at the end.

Now, let us see the COUNTIF function in action.

Task – Count the number of employees working in the IT department.

Let’s begin,

  • Select the desired cell
  • Type “=countif”
  • Choose the first option from the popup or press “Tab” on your keyboard
  • For the first argument (“range”), provide the cell reference as “B2:B10”
    (Note that the department for each employee is available in the column B)
  • Press the “,” key to move to the next argument
  • Type “IT”
    (Make sure to use the double quotation marks. This keyword will be tested against the values in the column B)
  • Complete the bracket using “)”
  • Press “Enter”

Here is the final formula,

=COUNTIF(B2:B10,"IT")

Google Sheets will instantly display “3” as an output using the above formula. It means three employees are working in the IT department.

Note that the Department column in our example contained textual values (letters) only. If you have a different dataset where the column contains numbers, you can use the COUNTIF function to find a count of cells containing a specific number.

This is a pretty powerful function in Google Sheets. In the case of numbers, make sure to use the less than (<) and greater than (>) symbols to perform complex calculations.

Let’s have a quick look at another example of using the COUNTIF function.

Q. How do you count the cells that do not contain a specific text string?

In contrast to the previous section, where we used the COUNTIF function to find the cells that contain specific text, now we will learn to use the function for the exact opposite purpose.

We will make use of the “<>” operator in Google Sheets.

Task – Count the number of employees who don’t work in the IT department.

Let’s begin,

  • Select the desired cell
  • Type “=countif”
  • Select the first option from the popup or press the “Tab” key
  • Provide the cell reference as “B2:B10” for the “range” argument
  • Press “,” to move to the next argument
  • In the double quotation marks, type “<>IT”
  • Complete the bracket using “)”
  • Press “Enter” on your keyboard

Here is the final formula,

=COUNTIF(B2:B10,"<>IT")

As per the output, out of 9 employees, 6 are not working in the IT department.

Q. What is another way to access the COUNTIF function in Google Sheets?

Formulas in Google Sheets can be inserted into the cells by selecting the cell and then either manually typing the formula or going to the main menu and selecting the “Insert” option.

Here are the steps to access the formula from the main menu:

  • Go to the main menu
  • Click on the “Insert” tab
  • Select the “Function” from the popup
  • Next, choose the “Math” option
  • Select the “COUNTIF” from the list of mathematical functions

As soon as you select the function from the list, Google Sheets will take you back to the cell and ask you to provide the values for the first argument of the function.

How to use the SUMPRODUCT function to count the cells that contain text

You can take this as an alternative to the COUNTA function that lets users count the number of cells that contain text strings.

The SUMPRODUCT function needs to be combined with the LEN function to count the cells that contain text.

Before we proceed further, let’s understand both of these functions.

Explained: The SUMPRODUCT Function in Google Sheets

The SUMPRODUCT function by Google Sheets is used to multiply items across the arrays and add them up.

The main use case of this formula is to find the total price.

Consider, for example, we have three columns as below,

The first one represents the product name, second one represents the total quantities sold over time, and the third one includes the individual product price.

To find the exact revenue generated by selling all those items, we need to multiply the individual item price by the quantity sold over time for each and then add them up.

The SUMPRODUCT function in Google Sheets is your way to go here!

Here’s the general syntax of the SUMPRODUCT function in Google Sheets,

=SUMPRODUCT(array1, [array2, …])

The arguments look similar because the formula deals with multiple arrays at a time. You can provide multiple columns as an array. Then, the function will multiply and add them up.

Let’s use this function to find the total revenue generated for the following table.

Here are the steps,

  • Type “=sumproduct”
  • Select the first option from the popup or press the “Tab” key
  • For the “array1” argument, provide the cell reference as “B2:B8”
  • Press the “,” key to move to the next argument
  • Provide the cell reference as “C2:C8”
  • Complete the bracket using “)”
  • Press “Enter”

Here’s the final formula,

=SUMPRODUCT(B2:B8,C2:C8)

Google Sheets has found the total revenue after selling those products in the mentioned quantity as “$5,900”.

To compare the results, I also have done manual calculations. Refer to the following image.

The important part is that we are not going to use the main working principle of the SUMPRODUCT function to find the total price. Instead, we will use its ability to count the cells holding the numbers and sum them up.

Explained: The LEN function in Google Sheets

The LEN is a simple formula that counts the number of characters within a text string.

Note that apart from the letters, the function counts the number of spaces and special characters as well.

Here’s the general syntax for the LEN function in Google Sheets,

=LEN(text)

Where the “text” argument needs to be replaced with the cell reference holding the text string.

Now, let us combine the SUMPRODUCT and LEN functions.

Task – Count the number of employees who have received the bonus in December 2023.

Here are the steps,

  • Select the desired cell
  • Type “=sumproduct”
  • Select the first option from the popup or press “Tab” key
  • Now, instead of providing any array, we will directly use the LEN function. Start by typing “(“
  • Then, type “len”

    (As the LEN is a function in Google Sheets, you will see a popup as shown above)
  • Select the first option from the popup
  • Provide the cell reference as “C2:C21”
  • Close the bracket for the LEN function using the “)”
  • We will create a criteria using the greater than operator. Type “>” using your keyboard
  • Type “0”, as the LEN function count should be greater than 0, which will indicate that the particular cell contains at least one character
  • Close the bracket for criteria using the “)”
  • Next, close the bracket for the SUMPRODUCT function using “)” again
  • Press “Enter”

Here is how the final formula should look,

=SUMPRODUCT((LEN(C2:C21)>0))

The formula has displayed that 13 employees received the bonus in December 2023.

You don’t need any expert knowledge to use the SUMPRODUCT function method to count the cells holding a text string.

But the COUNTA function is still quick and pretty straightforward!

Q. What is another way to access the SUMPRODUCT function in Google Sheets?

As with every Google Sheets function, you can select the desired cell and start typing the formula or go to the main menu and choose the function as discussed below:

Hover to the main menu

  • Click on the “Insert” tab
  • Select the “Function” option from the popup
  • Now, choose the “Array” from the available function categories
  • Click on the “SUMPRODUCT”

Your formula will be instantly inserted into the cell.

Compared to typing the formula, this method is quite lengthy.

Conclusion

Counting the cells that contain text is pretty straightforward in Google Sheets.

You don’t have to be an expert to use any of the above methods. The COUNTA is the best choice when it comes to differentiating the blank cells from the cells that contain the text string and finding the count.

I hope this article taught you everything about how to count cells that contain a text. Feel free to comment below if you are stuck somewhere or having any particular error using the COUNTA, COUNTIF, and SUMPRODUCT functions.

Also, don’t forget to check our blog. We have plenty of articles that guide you about using Google Sheets for organizing and analyzing datasets.