where data is the named range B5:B15. The result is 4, because there are four cells in the range B5:B15 that contain text values. Note: This formula counts cells that contain any text value. To count cells that contain specific text, see this formula. To count cells that are not empty (i.e. cells that contain text, numbers, dates, etc.), see this formula.
COUNTIF function
The simplest way to solve this problem is with the COUNTIF function and the asterisk () wildcard. The asterisk () matches zero or more characters of any kind. For example, to count cells in a range that begin with “a”, you can use COUNTIF like this: In this example however we don’t want to match any specific text value. We want to match all text values. To do this, we provide the asterisk (*) by itself for criteria. The formula in H5 is: The result is 4, because there are four cells in data (B5:B15) that contain text values. To reverse the operation of the formula and count all cells that do not contain text, add the not equal to (<>) logical operator like this: This is the formula used in cell H6. The result is 7, since there are seven cells in data (B5:B15) that do not contain text values.
COUNTIFS function
To apply more specific criteria, you can switch to the COUNTIFs function, which supports multiple conditions. For example, to count cells with text, but exclude cells that contain only a space character, you can use a formula like this: This formula will count cells that contain any text value except a single space (" “).
SUMPRODUCT function
Another way to solve this problem is to use the SUMPRODUCT function with the ISTEXT function. SUMPRODUCT makes it easy to perform a logical test on a range, then count the results. The test is performed with the ISTEXT function. True to its name, the ISTEXT function only returns TRUE when given a text value: To count cells with text values in the example shown, you can use a formula like this: Working from the inside out, the logical test is based on the ISTEXT function: Because data (B5:B15) contains 11 values, ISTEXT returns 11 results in an array like this: In this array, the TRUE values correspond to cells that contain text values, and the FALSE values represent cells that do not contain text. To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (–): The resulting array inside the SUMPRODUCT function looks like this: With a single array to process, SUMPRODUCT sums the array and returns 4 as the result. To reverse the formula and count all cells that do not contain text, you can nest the ISTEXT function inside the NOT function like this: The NOT function reverses the results from ISTEXT. The double negative (–) converts the array to numbers, and the array inside SUMPRODUCT looks like this: The result is 7, since there are seven cells in data (B5:B15) that do not contain text values. Note: the SUMPRODUCT formulas above may seem complex, but using Boolean operations in array formulas is powerful and flexible. It is also an important skill in modern functions like FILTER and XLOOKUP, which often use this technique to select the right data. The syntax used by COUNTIF on the other hand is unique to a group of eight functions and is therefore not as useful or portable.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
