How to Count Text in Excel (7 Easy Tricks)

In this article, we will explain how to count text in Excel in 7 different ways. Here’s an overview of the dataset we’ll use to demonstrate our methods.

Method 1 – Using the COUNTIF Function with the Asterisk (*) to Count Text Cells

The COUNTIF function counts the number of cells meeting a given condition. The Asterisk (*) is a wildcard character that represents any number of characters present in the cell. With the combination of these two, we can count text cells.

Here, we have a dataset (B4:B9) of sold items. The result will display in Cell D6.

Steps:

Select Cell D6 and enter the following formula:

=COUNTIF(B5:B9,"*")

Press Enter to see the result.

Method 2 – Counting Specific Text in Excel

The COUNTIF function can count the number of instances of a specific string in a range in Excel. Suppose we have a dataset (B4:B9) of sold items. We’ll find the total number of cells that contain the specific text “BKA”, and return the result in Cell D7.

Steps:

Select Cell D7.
Enter the following formula:

=COUNTIF(B5:B9,"*BKA*")

Press Enter to see the result.

We use a wildcard character, Asterisk (*) just before and after the criteria text to make sure that all the cells in the dataset containing the criteria text are taken into account.

Method 3 – Counting Case Sensitive Text

In the below dataset (B4:B9), we have some case-sensitive issues. For example the item name “CKDA” also appears in the “Ckda” form. Suppose we only need to count the cells that contain “CKDA”. We’ll use a case-sensitive Excel function, the EXACT function, along with the SUMPRODUCT function, and add a double minus sign, which converts TRUE and FALSE values into numeric 1’s and 0’s respectively for easy calculation.

Steps:

Select Cell D7.
Enter the following formula:

=SUMPRODUCT(--EXACT("CKDA",B5:B9))

Press Enter to see the result.

Method 4 – Using an Array Formula to Count Text in Excel

We use Excel Array Formulas for doing one or more powerful calculations multiple times in a selected range. Suppose we have a dataset (B4:B9) of sold items that contains a blank cell. We’ll create an array formula here to count text.

Steps:

Select Cell D6.
Enter the following formula:

=SUM(IF(ISTEXT(B5:B9),1))

Press CTRL + SHIFT + ENTER to see the result (or ENTER is you’re using Microsoft 365 version).

In the formula bar, the array formula will appear in curly brackets {}.

Method 5 – Combining the SUMPRODUCT and ISTEXT Functions to Count Text Cells

The ISTEXT function checks if the cell contains any text or not and returns TRUE or FALSE. The SUMPRODUCT function returns the sum of the products from multiplying arrays together.

Assuming we have a dataset (B4:B9) of sold items, we can use the ISTEXT function wrapped into the SUMPRODUCT function to count text cells. We must add a double minus sign like in the above procedure to convert the TRUE and FALSE values into 1’s and 0’s so that the SUMPRODUCT function can do its work.

Steps:

Select Cell D6.
Enter the following formula:

=SUMPRODUCT(--ISTEXT(B5:B9))

Press Enter to see the result.

Method 6 – Counting Partial Text in Excel with the COUNTIF Function

We don’t have to input the whole word in a function for searching purposes. Suppose we have a dataset (B4:B9) of sold items and want to find out the cells that contain “BKA” in the first portion.

Steps:

Select Cell D6.
Enter the following formula:

=COUNTIF(B5:B9,"BKA*")

Press Enter to see the result.

We use an Asterisk (*) in the criteria along with “BKA” to enable the COUNTIF function to count all the cells containing BKA and anything in front of it.

Method 7 – Counting Text Cells Excluding the Space Character

The COUNTIFS function counts the number of cells that meet multiple criteria. Suppose we have a dataset (B4:B9) of items that contains a space character in Cell B7. We’ll count the number of text cells excluding the space character using the COUNTIFS function.

Steps:

Select Cell D6.
Enter the following formula:

=COUNTIFS(B5:B9,"*",B5:B9,"<> ")

Press Enter, and the result is returned.

How to Count the Number of Characters of a String in Excel

We can determine how many characters are in a text cell by using the LEN function, which returns the length of the given text cell.

Suppose we have the same dataset (B4:B9) of sold items. We’ll count the characters in each text cell and return the result in the range C4:C9.