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**.

**Steps:**

- Select
**Cell C5**. - Enter the following formula:

`=LEN(B5)`

- Press
**Enter**.

- Use the
**Fill Handle**tool to**Autofill**the next cells

The result is as follows:

**Download Practice Workbook**

## Related Articles

- End of a Range Using VBA in Excel
- Excel VBA Copy Range to Another Sheet
- Excel Subscript Out of Range Error in VBA
- VBA for Each Cell in Range in Excel
- How to Use VBA Range Offset
- Use the Range Object of VBA in Excel