Microsoft Excel provides a wide range of useful functions and methods to count cells with text or any other characters and strings. In this article, you’ll get to learn all possible methods to **count cells with text by using an Excel formula** under different criteria.

## Download Practice Workbook

You can download our practice workbook here that we’ve used to prepare this article.

## 4 Simple Ways to Count Cells with Text Using Excel Formula

In this section of the article, we will learn 4 simple methods to count cells with text using Excel formula.

Not to mention that we have used the *Microsoft Excel 365 *version for this article; you can use any other version according to your convenience.

### 1. Using COUNTIF Function

To count cells with text using Excel formula under several criteria, the **COUNTIF function** does the best trick among all methods.

#### 1.1 Using Asterisk (*) to Count Cells with Text

Here, in **Column B**, different sorts of words and values are present. You need to count the cells containing text only.

**Steps:**

- Firstly, in cell
**D9**, where we’re going to find the result, type the following formula.

`=COUNTIF(B4:B13,"*")`

Here, the range **B4:B13** refers to the cells of the **COUNTIF with Asterisk (*) **column, and the **COUNTIF **function will count the cells within the range **B4:B13 **that meet our specified criteria.

- Then, press
**ENTER**.

So, the result will be returned as **4 **as it excludes **TRUE **and **FALSE**. **TRUE **and **FALSE **are** logical values** in Excel. This is why they won’t be counted as **Texts**. We’ll see later in the methods how we can bring back the texts- **TRUE **and **FALSE **too.

#### 1.2 Counting Text Cells with Partial Match

Now, we need to count cells that contain **“Ap” **among all texts. Let’s follow the instructions outlined below to do this.

**Steps:**

- In cell
**D9**, type the formula given below.

`=COUNTIF(B4:B13,"*Ap*")`

Here, the **COUNTIF **function will count the cells within the range **B4:B13 **that meet our specified criteria.

- Following that, press
**ENTER**.

So, among all the cells from **B4 **to **B13**,** 4** words are there that contain **“Ap” **inside the texts.

#### 1.3 Counting Text Cells with Exact Match

If we want to find the word **“Apple” **(not case-sensitive) only from the cells, we need to use the **COUNTIF** function of Excel. Let’s follow the steps mentioned below to do this.

**Steps:**

- Firstly, use the following formula in cell
**D5**.

`=COUNTIF(B4:B13,"Apple") `

**Now, **the **COUNTIF **function will count the cells within the range **B4:B13 **that meet our specified criteria.

- After that, press
**ENTER**.

So, this word has appeared exactly twice in the cells. This formula excludes **“Apple Juice”** and will look for exact matches for **“Apple” **only. But another fact is, this function is not case-sensitive. We’ll have a solution for this issue in another method in this article.

#### 1.4 Counting Text Cells with Cell References

We can also select a cell that contains a word and then look at how many times this appears in the whole column or a range of cells. There are **2 **cases here. We’ll search for a** partial match** first.

**Steps:**

- Firstly, in cell
**D7**, type the formula mentioned below.

`=COUNTIF(B4:B13,"*"&B5&"*")`

Here, cell **B5 **indicates the cell of criteria of the partial match and the **COUNTIF **function will count the cells within the range **B4:B13 **that meet the specified criteria in the above formula.

- After that, press
**ENTER**.

The resultant value will be **3 **as we’ve searched for the **text (apple)** lying in cell **B5 **in all cells, including itself.

- And if we want the
**exact match**, then we have to type the following formula in cell**D8**.

`=COUNTIF(B4:B13,B5) `

- Then, press
**ENTER**.

So here we’re referring to cell **B5**, which contains the word **“****apple”**. The result will return as **2 **since exactly this word has appeared in cells **B5 **and **B6 **only. It’ll exclude **B4**, which contains **2 **words**“Apple Juice”**.

#### 1.5 Using a Wildcard to Count Cells with Fixed Position of the Characters in Texts

Now here’s an interesting part. Let’s assume, we want to count the cells that contain a certain number of characters and the positions of characters will also be considered. So, as I’ve said, if we want to count the cells that contain exactly **5 **characters and the **5th **one will be **‘e’ **then the formula will be-

`=COUNTIF(B4:B13,"????e")`

We’ll get the count value as **2 **as in our range of cells, only two words (**apple **and **Apple**) have exactly **5 **characters, among which the **5th **one is **‘e’**. And the word **“Apple Juice”** has not been counted here because this whole text contains **11 **characters, including a space.

### A Minor Limitation of Using COUNTIF Function and How to Solve It?

The **COUNTIF **function is really flexible to use, but this function is unable to count more than **255 **characters in a string. So to solve this issue, you have to **CONCATENATE **two or more text strings by using **Ampersand (&)** between them. To learn more about this, you can have a look at this **recommendation from Microsoft**.

### 2. Using COUNTIFS to Count Cells with Text Considering Multiple Criteria

The **COUNTIFS function** is a subcategory of the **COUNTIF **function and this **COUNTIFS **will let you add multiple conditions to combine together.

Here, we’ll count the cells under **2 **criteria:

*Cells with Texts*and*Cells Excluding the Word***Apes**

To do this, we have to follow these simple steps-

- In Cell
**D9**, type the formula mentioned below.

`=COUNTIFS(B4:B13,"*",B4:B13,"<>Apes")`

Here, the **COUNTIF **function will count the cells within the range **B4:B13 **that meet the specified criteria.

- After that, press
**ENTER**.

So, now you’re obtaining the number of cells as **3 **under the mentioned criteria. And like before, as **TRUE **and **FALSE **are logical values in Excel, they will be excluded from being considered as texts.

**Read More:** **Count Cells that Contain Specific Text in Excel**

**Similar Readings**

**How to Count Blank Cells in Excel with Condition (3 Methods)****Count Odd and Even Numbers in Excel (3 Easy Ways)****How to Count Number of Cells with Dates in Excel (6 Ways)****Count Non Blank Cells with Condition in Excel (6 Methods)****How to Count Filled Cells in Excel Using VBA (7 Methods)**

### 3. Applying SUMPRODUCT Formula to Count Cells with Text

The** SUMPRODUCT function** is another useful function we can use to count cells with different types of requirements by integrating it with other functions. The **SUMPRODUCT **function is generally used to sum all the numbered values in an array that can contain a huge range of cells along the columns and rows.

#### 3.1 Merging ISTEXT and SUMPRODUCT Functions to Count Cells with Texts

Merging ISTEXT and SUMPRODUCT functions is a smart way to count cells with texts using Excel formula. We’ll use the same dataset here to insert this function. Now, let’s follow the procedure discussed in the following section of the article.

**Steps:**

- In cell D9, type the following formula.

`=SUMPRODUCT(--ISTEXT(B4:B13))`

- Now, press
**ENTER**.

**Formula Breakdown**

- The
**ISTEXT function**is a logical function here that finds out if the cells contain text strings (**TRUE**) or not (**FALSE**).

- Before this
**ISTEXT function**‘**–**’ (*Double Hyphen, known as Double Unary too*) has been used to convert the logical values gained from the**ISTEXT**function into**1**(**TRUE**) or**0**(**FALSE**).

- So, the
**SUMPRODUCT**function lying outside will then sum up all**1’s**and**0’s**and give you the result as count values. **Output**→**4**.

Consequently, you will see the following output on your worksheet, as demonstrated in the following image.

#### 3.2 Incorporating LEN and SUMPRODUCT Functions to Count Text and Non-Blank Cells

Now, we’ll incorporate **SUMPRODUCT **and **LEN** functions here. The **LEN **function determines or counts the characters in a string that contains a number of texts. So, if we get to know through this **LEN **function if a cell contains at least **1 **character, then we’ll understand that cell contains text (s) or number (s) or any other value (s). And the **SUMPRODUCT **function will be used here to count those cells like before.

**Steps:**

- Firstly, in cell
**D9**, type the formula given below.

`=SUMPRODUCT(--(LEN(B4:B13)>0))`

- Subsequently, press
**ENTER**.

**Formula Breakdown**

**LEN(B4:B13)>0)**→ The**LEN**function counts every single character including spaces too. It only excludes**blank cells**.- Before this
**LEN function**‘**–**’ (*Double Hyphen, known as Double Unary too*) has been used to convert the logical values gained from the**LEN**function into**1**(**TRUE**) or**0**(**FALSE**). - Finally, the
**SUMPRODUCT**function will then sum up all**1’s**and**0’s**and give you the result as count values. **Output**→**9**.

Here, you’ll see the result returning as **9** in cell **D5**.

#### 3.3 Combining SUMPRODUCT, FIND and ISNUMBER Functions to Count Cells with Case-Sensitive Texts

If we want to count text cells along with **case-sensitive criteria **then we have to merge **SUMPRODUCT**, **FIND** and **ISNUMBER** functions here. So, from our dataset, if we want to know how many times the word **‘Apple’ **(*with Uppercase*) has appeared in the cells, then we have to follow these steps.

**Steps:**

- Type the following formula in the output cell
**D9**.

`=SUMPRODUCT(--(ISNUMBER(FIND("Apple",B4:B13))))`

- Afterward, press
**ENTER**.

**Formula Breakdown**

**FIND(“Apple”,B4:B13)**→ The**FIND**function looks for a specific text or value mentioned inside a fixed range of cells or array.**ISNUMBER(FIND(“Apple”,B4:B13))**→ The**ISNUMBER**function is another logical function that determines if the string in a cell is a number(**TRUE**) or not(**FALSE**).- Before this
**ISNUMBER function**‘**–**’ (*Double Hyphen, known as Double Unary too*) has been used to convert the logical values gained from the**ISNUMBER**function into**1**(**TRUE**) or**0**(**FALSE**). - Lastly, the
**SUMPRODUCT**function will then sum up all**1’s**and**0’s**and give you the result as count values. **Output**→**2**.

You’ll see the resultant value as **2 **as this function has excluded the word **“apple” **(*with lowercase*) while executing the process.

#### 3.4 Counting Logical Texts by Using ISLOGICAL Function Inside

Now we’ll count how many **logical values** are present in our dataset if we want to make them count as texts too.

**Steps:**

- Firstly, in cell
**D8**type the formula given below.

`=SUMPRODUCT(--(ISLOGICAL(B4:B13)))`

- After that, hit
**ENTER**.

**Formula Breakdown**

- ISLOGICAL(B4:B13) → The
**ISLOGICAL function**determines if the characters in a cell return as a logical value (**TRUE**) or not (**FALSE**). - Before this
**ISLOGICAL function**‘**–**’ (*Double Hyphen, known as Double Unary too*) has been used to convert the logical values gained from the**ISLOGICAL**function into**1**(**TRUE**) or**0**(**FALSE**). - Lastly, the
**SUMPRODUCT**function will then sum up all**1’s**and**0’s**and give you the result as count values. **Output**→**2**.

So, you’re seeing the resultant value as **2 **as it counts only logical values from the range of cells.

*Note: **You might find Double-Hyphen or Double-Unary as a Long Dash here as text while reading this article, but in the pictures, you’ll find them with an appropriate view.*

**Read More:** **How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)**

### 4. Inserting Subtraction Formula with COUNTA and COUNT Functions to Count Cells with Text

And in our last method, we’re going to apply a simple subtraction formula between **2 ****COUNTA functions**. Here, the **COUNTA **function will count all the non-blank cells and the **COUNT function** will count all the cells with number strings. So, if we subtract the number of cells containing numerical values from the number of non-blank cells, we’ll have our required result.

**Steps:**

- Firstly, type the following formula in the output cell:

`=COUNTA(B4:B13)-COUNT(B4:B13)`

- Then, press
**ENTER**.

This method includes all cells containing logical values or strings too and that’s why we’ll get the result as **6**.

## How to Count Cells with Specific Text in Multiple Columns in Excel

In this section of the article, we will learn the steps to count cells with specific text in multiple columns. To do this we will simply use the **COUNTIF **function of Excel. Let’s use the steps outlined below to do this.

**Steps:**

- Firstly, enter the following formula in cell
**B11**.

`=COUNTIF(B4:C8,"Apple")`

Here, the range** B4:C8** indicates the range where we will search and count for the text **‘Apple’**.

- Following that, hit
**ENTER**.

Consequently, you will have the following output in cell **B11**. The output indicates that there are **2 **cells in the array where there is an exact match for the text **“Apple”**.

## How to Count Cells with Numbers in Excel

While working in Excel, we often need to count cells that contain numbers. We can do this simply by using the **COUNT **function of Excel. Let’s use the procedure discussed in the following section to do this.

**Steps:**

- Firstly, use the following formula in cell
**D5**.

`=COUNT(B4:B13)`

- Then, press
**ENTER**.

As a result, you will get the count of the cells that contain numbers in cell **D5 **as marked in the following picture.

You can also use **different ways to count cells with numbers in Excel**.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

## Concluding Words

These are all basic, easy and convenient techniques you can apply anytime in spreadsheets to **count cells with text by using an Excel formula** under different conditions. I hope, this article has helped you to learn all the methods to count cells with texts. If you have any questions or feedback then please comment here. You can also have a glance at our more interesting and informative articles on Excel functions and applications on this website.