Excel formulas to count cells with text: any, specific or filtered cells

How to count cells containing text in Excel?

Excel was originally designed to process numbers, but today we also often use it to store and process text. Want to know how many cells are in the worksheet that contain text? Microsoft Excel provides several functions to achieve this. Which function should be used? It depends on the situation. In this tutorial, you will find various formulas and the best time to use each formula.

• Statistics cells containing any text
• Ignore empty strings and spaces and count cells containing text
• Statistics of cells containing specific text
• Statistics filtered cells containing text

How to count the number of cells containing text in Excel

There are two basic formulas to find the number of cells that contain any text string or character within a given range.

Use COUNTIF formula to count all cells containing text

When you want to find the number of cells in Excel that contain text, using an asterisk in a conditional parameter, the best and easiest solution is:

COUNTIF(*范围*, "\*") Since the asterisk (*) is a wildcard that matches any character sequence, the formula will count all cells containing any text.

Statistics cells containing any text using the SUMPRODUCT formula

Another way to get the number of cells containing text is to use the SUMPRODUCT and ISTEXT functions in combination:

SUMPRODUCT(--ISTEXT(*范围*)) or

SUMPRODUCT(ISTEXT(*范围*)*1) The ISTEXT function checks whether each cell in the specified range contains any text characters and returns an array of TRUE (cells containing text) and FALSE (other cells) values. The double unary operator (--) or multiplication operation casts TRUE and FALSE to 1 and 0 respectively, thereby generating an array of 1 and 0. The SUMPRODUCT function sums all elements of the array and returns the number of 1, that is, the number of cells containing text.

To better understand how these formulas work, see which values ​​are counted and which are not counted:

Count content	Uncounted content
- Cells containing any text - Special characters - Numbers formatted as text - Cells that can be regarded as blank but contain empty strings (""), apostrophes ('), spaces, or invisible characters	- number - date - Logical values ​​of TRUE and FALSE - mistake - Blank cells

For example, to count cells containing text in the A2:A10 range (excluding numbers, dates, logical values, errors, and blank cells), you can use one of the following formulas:

=COUNTIF(A2:A10, "*")

=SUMPRODUCT(--ISTEXT(A2:A10))

=SUMPRODUCT(ISTEXT(A2:A10)*1)

The following figure shows the results:

Ignore spaces and empty strings and count cells containing text

The formula discussed above counts all cells containing any text characters. However, in some cases, this can be confusing because some cells may look blank but actually contain characters that the human eye cannot see, such as empty strings, apostrophes, spaces, line breaks, etc. As a result, cells that can be regarded as blank will be counted by formulas, causing the user to worry about trying to figure out the reason.

To exclude "false positive" blank cells from the count, use the COUNTIFS function and use the "exclude" character in the second condition.

For example, to count cells containing text in the A2:A7 range (ignore cells containing space characters ), use the following formula:

=COUNTIFS(A2:A7,"*", A2:A7, " ")

If the target range contains any formula-driven numbers, some formulas may result in an empty string (""). To ignore cells containing empty strings , replace "*" with "*?*" in the criteria1 parameter:

=COUNTIFS(A2:A9,"*?*", A2:A9, " ")

A question mark enclosed with an asterisk indicates that there should be at least one text character in the cell. Since an empty string has no characters, it does not meet the criteria and will not be counted. Blank cells starting with the apostrophe (') are also not counted.

In the figure below, there is a space in A7, an apostrophe in A8, and an empty string in A9 (=""). Our formula ignores all these cells and returns a text cell count of 3:

How to count cells containing specific text in Excel

To get the number of cells that contain a specific text or character, just provide the text in the conditional parameters of the COUNTIF function. The following example explains the subtleties.

To match the sample text exactly, enter the full text in quotes:

COUNTIF(*范围*, "*文本*") To count partially matched cells, place the text between two asterisks, which represent any number of characters before and after the text:

COUNTIF(*范围*, "\**文本*\*") For example, to find out how many cells in the A2:A7 range contain the word "bananas", use the following formula:

=COUNTIF(A2:A7, "bananas")

To count all cells that contain "bananas" as part of their content (regardless of location), use the following formula:

=COUNTIF(A2:A7, "*bananas*")

To make the formula easier to use, you can place the condition in a predefined cell, such as D2, and place the cell reference in the second parameter:

=COUNTIF(A2:A7, D2)

Based on the input in D2, the formula can fully or partially match the example text:

• For an exact match, type the same word or phrase as the full word or phrase displayed in the source table, such as Bananas .
• For partial matches, type sample text surrounded by wildcard characters, such as *Bananas* .

Since the formula is case-insensitive , you don't have to worry about letter case, which means *bananas* is OK too.

Alternatively, to count partially matched cells, join cell references and wildcard characters, for example:

=COUNTIF(A2:A7, "*"&D2&"*")

For more information, see How to count cells containing specific text in Excel.

How to count filtered cells containing text in Excel

When using Excel filters to display only relevant data for a given moment, sometimes you may need to count visible cells containing text . Unfortunately, there is no one-click solution for this task, but the following example will walk you through the steps.

Suppose you have a table as shown in the following figure. Some entries were extracted from a larger database using formulas and some errors occurred in the process. You want to find the total number of items in column A. The COUNTIF formula we use to count cells containing text is very effective when all rows are visible:

=COUNTIF(A2:A10, "*")

Now you narrow down the list based on certain criteria, such as filtering out items with a number of more than 10. The question is – how many projects are left?

To count the filtered cells containing text , you need to do the following:

1. In the source table, make all rows visible. To do this, clear all filters and unhide hidden rows.

2. Add a helper column containing the SUBTOTAL formula that indicates whether the row has been filtered. To process filtered cells , use 3 for the function_num parameter: =SUBTOTAL(3, A2)

   To identify all hidden cells (filtered and manually hidden), enter 103 in function_num :

   =SUBTOTAL(103, A2)

   In this example, we want to count visible cells containing text , regardless of how other cells are hidden, so we enter the second formula in A2 and copy it to A10.

   For visible cells, the formula returns 1. Once you filter out or hide certain rows manually, the formula returns 0 for them. (You won't see these zeros because they are returned for hidden rows. To make sure it works this way, just copy the contents of the hidden cell containing the Subtotal formula to any visible cell, e.g. =D2, assuming row 2 is hidden.)

   

3. Use the COUNTIFS function and two different criteria_range / criteria pairs to count visible cells containing text:

   • Condition 1 - Search for cells containing any text ("*") in the A2:A10 range.
   • Condition 2 - Search 1 in the range D2:D10 to detect visible cells.

   =COUNTIFS(A2:A10, "*", D2:D10, 1)

Now you can filter the data as you want, and the formula will tell you the number of filtered cells that contain text in column A (3 in this case):

If you don't want to insert extra columns into your worksheet, you will need a longer formula to complete the task. Just select the one you prefer:

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

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), -- (ISTEXT(A2:A10)))

Multiplication operators can also be:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))) * (ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10)-MIN(ROW(A2:A10)),,1)) * (ISTEXT(A2:A10)))

Which formula to use depends on your personal preferences – the results are the same anyway:

How these formulas work

The first formula uses the INDIRECT function to "provid" a single reference to SUBTOTAL for all cells in the specified range. The second formula uses a combination of OFFSET, ROW, and MIN functions to achieve the same purpose.

The SUBTOTAL function returns an array of 1 and 0, where 1 represents visible cells and 0 represents hidden cells (such as the auxiliary column above).

The ISTEXT function checks each cell in A2:A10, and returns TRUE if the cell contains text, otherwise returns FALSE. Double unary operator (--) casts TRUE and FALSE values ​​to 1 and 0. At this point, the formula is as follows:

=SUMPRODUCT({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})

The SUMPRODUCT function first multiplies elements at the same position in two arrays, and then sums the resulting array.

Since the result of multiplying by zero is zero, only cells represented by 1 in both arrays have 1 in the final array.

=SUMPRODUCT({0;1;1;0;0;1;0;0;0})

The number of 1 in the above array is the number of visible cells containing text.

This is how to count cells containing text in Excel. Thank you for reading, and hope to see you on our blog next week!

Downloadable files

Formulas for counting cells containing text in Excel

The above is the detailed content of Excel formulas to count cells with text: any, specific or filtered cells. For more information, please follow other related articles on the PHP Chinese website!