Skip to Content

How to Count Cells with Text in Excel: Complete Guide

Posted on Last updated:

Excel is a top spreadsheet application for analyzing massive datasets in tabular form. You can easily count the number of cells containing a specific text or string using built-in functions like COUNTIF and COUNTA.

Of course, if the dataset is small, you can manually do it.

But what if there is a large dataset and manual counting becomes impractical?

Worry not, because Excel has got you covered. 

This is a quick guide that discusses how to count cells with text in Excel using a set of formulas. Make sure to read the article to the end so you don’t miss any critical steps or information about the functions used.

Download the Example Excel Sheet

Please consider downloading the following spreadsheet to practice the Excel features and functions discussed in the following sections. It includes sheets and tables used to demonstrate counting cells containing specific text.

Click Here To Download!

If you have the required data ready in your own spreadsheet, skip downloading the above file.

How to Count cell that contain text in Excel

Excel provides hundreds of formulas to perform various calculations and tasks.

Compared to all available functions and tricks, the COUNTA function method is the quickest to count cells containing text.

One can also say that the COUNTA function is famous for counting the non-blank cells.

Explained: The COUNTA Function in Excel

The COUNTA function counts the cells that hold letters and numbers. It is a simple array formula in Excel with the following syntax.

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

Where value1, value2, and so on are the arguments that represent the cells you wish to count. It can be the cell references or ranges where you want to count the non-blank cells.

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 entirely blank are ignored by this function.

For example, consider the following formula:

=COUNTA(A1:A10)

It will count the cells with values (non-blank) in the range A1 to A10.

Note that you can also count the non-empty cells in several non-adjacent ranges. Let’s assume we need to count the cells in columns A and B. Here’s how the formula should be,

=COUNTA(A:A,B:B)

Next, consider the following dataset, which contains employee details, including their full name, annual salary, and bonus obtained in December.

Our task is to count the number of employees who received a bonus in December 2024.

Here are the steps to count cells that contain text.

  • Create a new table as shown below,
  • Click on the cell “G1”
    (It is the cell where we wish to display the count of employees who received the bonus in 2024. You can also choose to count the blank cell at the end of the column)
  • Type “=COUNTA”
  • Select the first option from the popup or press the “Tab” key
  • Select the cell reference starting from “C2:C21”
    (Note that the bonus information is mentioned in the column C)
  • Complete the bracket using “)” on your keyboard
  • Press the “Enter” button

The final formula looks like,

=COUNTA(C2:C21)

Excel will instantly display 13 as the result, as 13 employees have received the bonus in December 2024.

Note that our column (Bonus) contained the numerical values in the above example. However, you can use the COUNTA function in Excel if the column contains textual values or a mix of textual and numerical values.

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

There is one more alternative to inserting any formula in Excel other than manually typing it. 

Here’s how you can insert the COUNTA function,

  • Open the desired spreadsheet
  • Select the cell where you wish to insert the function
  • Go to the main menu
  • Click on the “Formulas” tab
  • Select “Insert Function”
  • In the search bar of the “Insert Function” dialog box, type the formula you wish to insert
    (Here, we will be inserting the function “COUNTA”, so type COUNTA in the search box)
  • Click on “Go”
  • Excel will recommend the related formulas to choose from, as shown below
  • After you select the desired formula, click on “OK”
  • You will now be directed to a new dialog box, “Function Arguments,” where you will be prompted to fill in the values for the arguments as shown

That’s it! This is a pretty long process compared to selecting the cell and typing the formula.

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

Difference between the COUNT and COUNTA functions in Excel

The COUNT and COUNTA functions in Excel perform similar jobs.

But they differ in what they count.

The COUNT function counts only the cells that contain numerical values

The COUNTA functions count all the non-blank cells, irrespective of their data type, including numbers, letters, logical values, errors, etc.

Consider the following example,

There are ten employees to whom we have paid an annual bonus in December 2024.

Of those ten people, only seven received the bonus, and the remaining three didn’t. As shown in the above image, those three people are mentioned with the keyword “NO BONUS.”

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, excluding the “NO BONUS” cells.

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

How to count Cells that contain a specific text string in Excel

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 finding cells containing a specific text string or numerical value.

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

Explained: The COUNTIF Function

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

As the name suggests, this function combines the COUNT and IF functions in Excel. It allows you to count the number of times a specific condition has been met.

Here’s the general syntax of the COUNTIF function in Excel,

=COUNTIF(range,“text”)

Let’s understand the COUNTIF function arguments,

  • “range” – This argument represents the cell range you wish to test against certain conditions or criteria. You need to provide the cell reference for this argument
  • “text” – You need to replace this argument with a specific text that will be searched for in the data range specified in the “range” argument. Make sure to enter the text string in 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 this article’s sake, we focus on finding text strings within multiple cells. So, make sure to use double quotation marks when 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” in place of the “text” argument
    (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 the “Enter” button

Here is the final formula,

=COUNTIF(B2:B10,”IT”)

After you follow the above steps, Excel will instantly display the count as “3”, meaning 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 Excel. In the case of numbers, make sure to use the less than (<) and greater than (>) symbols to perform complex calculations.

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

How to count the cells that do not contain a specific text string

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

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

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, six are not working in the IT department.

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 text cells.

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

Explained: The SUMPRODUCT Function in Excel

The SUMPRODUCT function in Excel first multiplies the elements of both arrays in the same positions and then adds the resulting array. This formula’s primary use case is to find the sum of the values from the given cell.

For example, we have three columns, as shown below. The first column represents the product name, the second represents the total quantities sold over time, and the third includes the individual product price.

To find the 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.

Here’s the general syntax of the SUMPRODUCT function in Excel,

=SUMPRODUCT(array1, [array2, …])

The arguments look similar because the formula deals with multiple arrays simultaneously. You can provide various 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 the “Enter” button

Here’s the final formula,

=SUMPRODUCT(B2:B8,C2:C8)

Excel 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 below.

The important part is that we will not use the main working principle of the SUMPRODUCT function, which is 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 Excel

The LEN function in Excel returns the length of a given text string as the number of characters.

This function counts characters in a string, including letters, numbers, special characters, and even all the spaces. 

Here’s the general syntax for the LEN function in Excel,

=LEN(text)

The “text” argument needs to be replaced with the text string whose length we wish to calculate. We can also replace it with the cell reference holding the text string.

Next, let us combine the SUMPRODUCT and LEN functions to count the cells holding text strings or numerical values.

Consider the following example. Our task is to count the number of employees who have received the bonus in December 2024.

Here are the steps,

  • Create a new table as shown below
  • Select the cell “G1”
  • 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. You need to start by typing “(“ on your keyboard
  • Then, type “LEN”
    (As the LEN is a function in Excel, 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 the “Enter” button

The final formula would be,

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

As shown in the above GIF, the formula indicates that 13 employees received the bonus in December 2024.

Note that you don’t need 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!

To Summarize: How To Count Cells With Text In Excel

Counting the cells that contain textual or numerical values is pretty straightforward in Excel.

You can do it within a few seconds by mastering the formulas and combinations of functions discussed above. By efficiently identifying and counting non-empty cells, you can quickly access the amount of data and track the changes.

The COUNTA is the best choice for differentiating the blank cells from those containing the text string and finding the count.

I hope this article taught you how to count cells containing a text. Please comment below if you are stuck somewhere or having any particular issue; we will try to answer your query as soon as possible.

Also, make sure to explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.