Can Excel Count Cells with Text? [Easy 2024 Guide]

Excel offers many ways to tackle any challenge, including the seemingly simple task of counting cells with text.

Throughout this article, we’ll show you various strategies that you can use to have Excel count cells with text. Continue reading to learn how.

How To Count Cells with Text in Excel

There are several ways to count cells with text in Excel:

1. Using the COUNTIF function
2. Using the SUMPRODUCT function
3. Using Filter tool

In this article, we give you a step-by-step guide in Excel on how to count cells with text using the methods above.

Download Our Example Worksheet for Excel

Before we go through all the features Excel can use to count if a cell contains text, you can download the example worksheet below to follow along with the tutorial.

Access Worksheet

How To Count Text Using the COUNTIF Function

The COUNTIF function allows you to count cells that meet specific requirements. You can use it to count cells containing text by providing the criteria as a text string within double quotes.

The COUNTIF syntax is as follows:

=COUNTIF(range, criteria)

Range: This refers to the range of cells you want to evaluate for the given criteria. It can be a single cell, a range of cells, or a named range.

Criteria: This is the condition or criteria that determines which cells to count. It can be a number, text, logical expression, or a cell reference containing the criteria. You can also use the Excel wildcard characters:

• ? for any single character.
• * for any number of characters.
• ~ to represent question marks or an asterisk.

However, in this guide, we’ll use the COUNTIF criteria to count the text in a cell.

The COUNTIF function needs a condition to count text in Excel. To count all the text in your data range, use the asterisk wildcard as the condition.

Here’s how to count if a cell contains text in Excel using the COUNTIF function:

1. Open your Excel worksheet and select the cell where you want the count to appear.
2. In the selected cell, enter the equals sign (=) and the COUNTIF function.

 

3. Select the range you want to count text in.

 

4. Add a comma and the asterisk sign (*) in quotation marks.

 

5. Press “Enter.”

 

The Excel formula to count cells with text for our example is:

=COUNTIF(A2:A11, "*")

The cell will display the total count of cells that contain text within the specified range.

The COUNTIF function evaluates each cell in the range and counts the ones that match the specified criteria, in this case, any text.

You can also use other methods like the ISTEXT function or filter the range to have Excel COUNTIF a cell contains text by displaying the cells with text before using the COUNTIF function.

How To Count Specific Text Using the COUNTIF Function

The COUNTIF text formula in Excel is similar to counting cells with specific text in Google Sheets. Here’s how to count if a cell contains specific text in Excel:

1. Click on the cell where you want the count to appear.
2. In the selected cell, enter the equals sign (=) and the COUNTIF function.

 

3. Select the range you want to count text in.

 

4. Add a comma, and in quotation marks, enter the text you want to count.
5. Close the brackets and press “Enter.”

 

The formula we used is:

 =COUNTIF(A1:A11, "Louis").

The COUNTIF function checks each cell within the range and counts the cells that match the specified criteria. In this case, it is the specific text you want to count. If you want to count cells with text that partially matches a specific text, you can use wildcard characters with the COUNTIF function again.

For example, to count cells containing any text that starts with the word Mary, you can use the following formula:

=COUNTIF(range, "Mary*")

The asterisk acts as a wildcard character that matches any number of characters after or before a word.

Using the COUNTIF function in this way allows you to count the occurrences of specific text within a range, even if it only takes up part of the cell, providing valuable insights into your data.

How To Count Cells with Text Using the SUMPRODUCT Function

Although primarily used for calculations, the SUMPRODUCT function can be utilized to count cells with text.

The syntax for the SUMPRODUCT function is as follows:

=SUMPRODUCT(array1, [array2], [array3], ...)

• array1: This is a required argument representing the first array or range to be multiplied and summed.
• [array2], [array3], …: These are optional arguments. You can include additional arrays or ranges to be multiplied and summed and can have up to 255 arrays in total.

The function multiplies relevant elements within each array when it receives one or more arrays or parameter ranges and then sums up the results. As long as the arrays have compatible multiplication dimensions, they might be the same or different sizes. However, the arrays should have multiple dimensions that can be multiplied together or have the same amount of rows and columns.

Using an array formula, you can check each cell within a range for text and sum the occurrences. However, you’ll need to add several more functions, including the SUBTOTAL function, the INDIRECT function, and the ISTEXT function.

Here’s how to count cells with text using the SUMPRODUCT function:

1. Select the cell where you want the count to appear.
2. In the selected cell, enter the equals sign (=) and the SUMPRODUCT function.

 

3. Enter the SUBTOTAL function.

 

4. Add the function number 103 used for counting non-blank cells.

 

5. Add the INDIRECT function.

 

6. Select the range and close the brackets.

 

7. Add a comma and the ISTEXT function in brackets.

 

8. Select the range you want to count text in.
9. Close the brackets and press “Enter.”

 

The full formula we used is as follows:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A11))), --(ISTEXT(A2:A11)))

This complex formula returns the number of cells with text in your specified range. Feel free to copy and paste it and edit the ranges.

How To Count Case-Sensitive Text in Excel

The SUMPRODUCT function can also be used to count case-sensitive text when combined with the ISNUMBER function and the FIND function.

For this tutorial, we’ll be using the following worksheet:

 

Here’s how:

1. Select the cell where you want the count to appear.
2. In the selected cell, enter the equals sign (=) and the SUMPRODUCT function.

 

3. After the opening brackets, add the ISNUMBER function.
4. Add the FIND function.
5. In the brackets, enter the text you want to find.
6. Add a comma and select the data range.
7. Close the brackets and press “Enter.”

 

The formula we’ve used for this is:

=SUMPRODUCT(--ISNUMBER(FIND("jane",A2:A11)))

This formula returns 2. In our example, this indicates the number of times the word, jane, appears in lowercase letters. It ignores the word Jane with the capitalized first letter.

The ISNUMBER function helps to prevent the formula from returning an error. It replaces the error values with the Boolean statements TRUE or FALSE in the formula.

However, the SUMPRODUCT function does not work with Boolean statements in the text. That’s why we added the double hyphen (– –) to convert them to numeric values 1 and 0.

If this is a little tricky to wrap your head around, don’t worry. Just focus on remembering the following steps.

How To Count Cells with Text Using Filters

You can filter a column or range by text to display the cells containing specific text only. Once filtered, you can check the row count to determine the number of cells with text.

In short, you must select the range, go to the “Data” tab, click the “Filter” button, and filter according to the desired text.

Here is a simple step-by-step guide on how to count text in Excel using the filter feature:

1. Select the range of cells that you want to count the text in.

 

2. In the Excel ribbon, click on the “Data” tab.

 

3. Click on the “Filter” button. Once you’ve done that, filter drop-down options will be added to each column header in your selected range.

 

4. Go to the filter drop-down for the column you want to count text in.

 

5. In the filter menu, uncheck the “Select All” option to deselect all values.

 

6. Scroll down the list and find the text values. Check the box next to it to show only the cells with text.

 

7. Now, you should only see the cells with the text displayed in the filtered range.

You can quickly ascertain the number of cells with text by applying the filter to your range and eliminating the blank cells. The text-containing cells are displayed more precisely in the filtered view, and the number of visible cells in the filtered range is indicated in the status bar.

You can then count the cells with text using a function like the COUNTA function or the COUNTIF function for specific text.

How To Count Text Cells with Multiple Criteria

To count text cells with multiple criteria, you must use the SUMPRODUCT function to multiply corresponding elements from each range and then sum the products. You’ll need to combine it with the ISNUMBER and the FIND function.

A formula to count cells with text and multiple criteria looks like this:

=SUMPRODUCT(--(ISNUMBER(FIND("criteria", range))+ ISNUMBER(FIND("criteria", range))>0))

You can add more ranges and criteria to the above formula too. For example:

=SUMPRODUCT(--(ISNUMBER(FIND("criteria", range))+ ISNUMBER(FIND("criteria", range)+ ISNUMBER(FIND("criteria", range)+ ISNUMBER(FIND("criteria", range))>0))

But let’s keep it simple for our example.

We’ll be using the following example sheet to count cells with text and multiple criteria:

 

Here’s how you can do it:

1. Select the cell where you want the count to appear.
2. In the selected cell, enter the equals sign (=), the SUMPRODUCT function, and two hyphens (–).

 

3. Add the ISNUMBER function and the FIND function.
4. Write the first criteria in quotation marks and select the data range.

 

5. Close the brackets and add the addition symbol (+).
6. Add in the ISNUMBER function and the FIND function again.
7. Add the second text criteria in quotation marks and select the second range.
8. Close the brackets and press “Enter.”

 

In our example above, we used the formula:

=SUMPRODUCT(--(ISNUMBER(FIND("jane",A2:A11))+ISNUMBER(FIND("Peters",B2:B11))>0))

This formula returns the number of cells either with the words, jane or Peters.

Using this combination of functions allows you to count text cells that meet multiple criteria simultaneously. It also provides a flexible and powerful method for analyzing data and obtaining precise counts based on your specific conditions.

Related: The Best Excel Courses

How To Count Cells with Text Excluding Blank Cells in Excel

To count cells with text in Excel using the COUNTIF function, follow these steps:

1. Open your Excel worksheet and select the cell where you want the count to appear.
2. In the selected cell, enter the equals sign (=) and the COUNTIF function.

 

3. Select the range you want to count text in.

 

4. Add a comma and the asterisk sign (*) in quotation marks.

 

5. Press “Enter.”

 

The formula for our example is:

=COUNTIF(A2:A10, "*")

The cell will display the count of cells that contain any text within the specified range.

The COUNTIF function evaluates each cell in the range and counts the ones that match the specified criteria, in this case, any text. It, therefore, has ignored blank cells.

Excel Count Cells with Specific Text from Another Sheet

If you want to count cells with specific text from another sheet in Excel, you can use the COUNTIF function combined with the sheet name and cell references. Here’s how you can do it:

1. Select the cell where you want the count to appear in your destination sheet.
2. In the selected cell, enter the equals sign (=) and the COUNTIF function.

 

3. Go to the sheets with the data and select the data range.

 

4. Add a comma and write the text in quotation marks.
5. Close the brackets and press “Enter.”

 

The formula we’ve used is:

=COUNTIF(Sheet!A2:A11, "Jane")

By referencing the sheet name followed by an exclamation mark (!) and providing the appropriate range, you can calculate data from a different sheet within your workbook.

The COUNTIF function then checks each cell within the specified range and counts the cells that match the specified text.

Frequently Asked Questions

How Do You Count if a Cell Contains Text but Not Specific Text?

By default, the COUNTIF function counts values based on a condition. If you want to count text in Excel using this function, you must add a value as the criteria.

To count all the text in your data range, you can use the asterisk (*) wildcard as the condition instead of a specific text, like so:

=COUNTIF(range, “*”)

Can Excel Count Cells With Text?

Yes, you have to use the COUNTIF formula with the following syntax:

=COUNTIF(range,criteria) 

Make the criteria either the specific text you want the cells to contain to count them inside quotation marks or use the asterisk (*) operator inside quotation marks if you want to count if a cell contains any text.

Wrapping Up

There are a few functions and tools in Excel that you can use to count cells with text. In this article, we’ve taken you through how to do this using the COUNTIF and SUMPRODUCT functions. We’ve also shown you how to use Filter and conditional formatting to have Excel count cells with text.

If you found this guide useful, make sure you also check out our premium template library and remember to use the code SSP to save 50%!

Get Premium Templates

Related:

• How To Calculate Age in Excel [12 Easy Examples]
• 4 Easy Examples of Greater Than or Equal To in Excel
• How To Pull Data From Another Sheet in Excel
• How To Solve the #SPILL Error in Excel [Easy Guide]