How to Count Cells with Text in Excel? 3 Easy Ways!

One of the essential Excel operations to summarize data is counting the number of cells in a dataset containing particular data types.

It’s a common task for many Excel users to count how many cells contain text strings in a column or row.

This could be useful when you have some cells that are blank in a column and you want to know how many cells contain text values and are not blank.

Or if you have different types of data sets (such as text, numbers, or dates) and you want to count how many cells contain only text values.

In this tutorial, I will show you some simple Excel formulas you can use to quickly count the number of cells that contain text values.

Table of Contents

Example Dataset For this Article

In this tutorial, to show how we can count cells with text in Excel, we use the example dataset below that we imported into Excel from an external database.

The dataset contains miscellaneous data, including text strings. By default, text strings are left-aligned in Excel. The text strings in the dataset are in the following forms:

Text Strings

Text strings are alphanumeric characters, for example, “Europe” and “Thomas2.”

Empty Strings

A cell that contains an empty string looks blank, but if you select the cell and look in the formula bar, you notice that it has an empty string (=””) as seen in the example below:

Apostrophe

The cell that contains an apostrophe looks blank, but when you select the cell and observe the formula bar, you notice that it has an apostrophe (‘), as seen in the example below:

Logical values

The logical values can be either TRUE or FALSE. While these look like text strings, Excel does not consider their text.

Special Characters

The special characters are non-alphanumeric, such as the @ and $ symbols.

Also read: How to COUNTIF Partial Match in Excel?

Quick Overview of Wildcard Characters (which we will use to Count Cells with Text Values)

We can use wildcard characters in formulas to count cells with text values in Excel.

Excel supports the following three wildcard characters that are used to look for and match particular patterns of text within a cell or a cell range:

The question mark (?) – It matches any single character. For example, f?ll could mean fill, fall, fell, full, and so on.
The asterisk (*) – The asterisk character matches any sequence of characters, including none. For example, A* could mean Africa, Asia, Aunt, A, etc.
The tilde (~) We use it to search for literal asterisks, question marks, or other tilde characters.

Now let’s dive into the tutorial and see the various techniques we can apply involving the wildcard characters to count cells with text in Excel.

Method #1: Use the COUNTIF Function to Count Cells With Text in Excel (Include Cells With Apostrophe and Empty Strings and Exclude Blank Cells)

We use a formula that involves the COUNTIF function to count cells with text in a cell range, including those with an apostrophe and empty strings.

The COUNTIF function calculates the number of cells within a cell range that satisfy the given condition.

We use our example dataset below to show how the COUNTIF formula can count cells with text, including empty strings and apostrophes, and exclude blank cells.

data set where we need to count cells that contain text values

We use the following steps:

Select cell C5 and type in the formula below:

=COUNTIF(A2:A17,"*")

county formula to count cells that contain text values

Press the Enter key.

The count of the cells in the dataset containing text values, an apostrophe, and an empty string is displayed in cell C5.

The blank cell and those holding the numeric and logical values are not counted by this formula.

Also read: Count Cells Less than a Value in Excel (COUNTIF Less)

Method #2: Use the COUNTIF Function to Count Cells With Text in Excel (Exclude Cells With Apostrophe and Empty Strings and Blank Cells)

We can use a COUNTIF formula to count cells with text in a dataset, excluding blank cells and those containing empty strings and apostrophes.

We use the COUNTIF formula in our example dataset below to show how to count cells with text, excluding blank cells and those containing empty strings and apostrophes.

We use the below steps:

Select cell C5 and type in the below formula:

=COUNTIF(A2:A17,"?*")

Note: The criteria argument combines the question mark and asterisk wildcard characters, meaning that the qualifying cells must include at least one character to be considered for the counting.

county formula with two wild card characters

Press the Enter key.

The count of cells containing text values, excluding the cells having an apostrophe and an empty string, is displayed in cell C5.

Blank cells and those containing numeric and logical values are not counted by default.

a result of the counter formula that counts cells with text

Also read: How to Sum a Column in Excel?

Method #3: Combine COUNTIF, SUMPRODUCT, and ISLOGICAL Functions to Count Cells With Text and Logical Values in Excel (Exclude Cells With Apostrophe and Empty Strings and Blank Cells)

Suppose we want to count cells with text values and include the cells containing logical values, excluding empty cells and those cells with apostrophes and empty strings.

In that case, we must use a formula that combines COUNTIF, SUMPRODUCT, and ISLOGICAL functions.

We use a formula that combines the COUNTIF, SUMPRODUCT, and ISLOGICAL functions in the example dataset below to show how to count cells with text and logical values, excluding blank cells and those containing empty strings and apostrophes.

We proceed as follows:

Select cell C5 and type in the formula below:

=COUNTIF(A2:A17,"?*")+SUMPRODUCT(--ISLOGICAL(A2:A17))

COUNTIF formula to count cells with logical values

Press the Enter key.

The count of cells containing text and logical values, excluding blank cells and those containing empty strings and apostrophes, is displayed in cell C5.

The cells containing numeric values are not counted by default.

Explanation of the formula

=COUNTIF(A2:A17,”?*”)+SUMPRODUCT(–ISLOGICAL(A2:A17))

COUNTIF(A2:A17,”?*”) This first part of the formula combines the question mark and asterisk wildcard characters in the criteria argument, meaning that the cells that qualify to be counted must include at least one character. The formula returns the count of cells containing text values with at least one character. In this case, the count is 8.
SUMPRODUCT(–ISLOGICAL(A2:A17) In this second segment of the formula, the ISLOGICAL function checks for logical values and returns TRUE if it finds a logical value and FALSE if it does not. The double negative signs that precede the ISLOGICAL function convert the FALSE values to zeroes (0) and the TRUE values to ones (1). The SUMPRODUCT function sums the zeroes and ones and returns the count of cells containing logical values. In this case, the count is 2.
The value returned by the first part of the formula is added to the count produced by the second part of the formula, and the resultant value of 10 is displayed in cell C5.

This tutorial showed three techniques for counting cells with text in Excel. We hope you found the tutorial helpful.

In the first method, I showed how to use the COUNTIF formula with an asterisk character to count all the cells that contain text values.

In the methods after that, I have also shown how to modify the formula so that blank cells or cells with apostrophes are not counted.

And in method 3, I have also shown how you can also use the SUMPRODUCT function to ignore logical values (TRUE and FALSE).

I hope this tutorial has been useful and you have learned how to the number of cells that contain text strings in Excel under various scenarios.

Other Excel articles you may also enjoy reading: