In this article, I’ll show how you can count if a cell contains text in Excel. I’ll show you to count the number of cells that contain texts, as well as the number of cells that contain texts but include or exclude a specific text value.

**Table of Contents**hide

**Download Practice Workbook**

**5 Easy Approaches to Count If Cell Contains Text in Excel**

Here we’ve got a data set with the **Names** of some customers and their **Contact Addresses** of a company called Sunflower Kindergarten.

Today our objective is to count how many addresses among the contact addresses are **Email** Addresses.

That means we shall count an address if it is a text, not a number.

**1. Use COUNTIF Function to Count If Cell Contains Text in Excel**

You can use the **COUNTIF function** of Excel to count the number of cells that contain texts.

To accomplish that, use an **Asterisk Symbol (*)** as the **criteria** of the **COUNTIF ****function**.

Therefore, the formula to count the total number of **Email** Addresses will be:

`=COUNTIF(C4:C13,"*")`

**[**Here **C4:C13** is the range of my contact addresses. You insert it according to your need.]

Look, it has counted the total number of text addresses, that is, **Email** addresses.

The result is **7**.

**2. Combine ISTEXT and SUMPRODUCT Functions to Count If Cell Contains Text in Excel**

You can also use a combination of the **ISTEXT function** and the **SUMPRODUCT function** to count cells that contain text values in Excel.

To count the number of **Email** Addresses in this way, select any cell in your data set and enter this formula:

`=SUMPRODUCT(--ISTEXT(C4:C13))`

**[**Here **C4:C13** is the range of my contact addresses. You insert it according to your need.]

Look, we have again counted the number of text addresses successfully. And that is **7**.

**⧪ Explanation of the Formula:**

**ISTEXT(C4:C13)**checks each cell of the range**C4:C13**and returns a**TRUE**if the cell contains a text. Otherwise, it returns a**FALSE**.- Thus
**ISTEXT(C4:C13)**returns an array of boolean values,**TRUE**and**FALSE**. **–ISTEXT(C4:C13)**converts the array returned by the**ISTEXT function**into an array of 1’s and 0’s.- It converts the
**TRUE**into**1**and the**FALSE**into**0**. - Finally, the
**SUMPRODUCT function**returns the sum of the total range. That is, it returns the number of 1’s in the range. - Thus the formula returns the number of cells that contain text values within the range.

**3. Use COUNTIF Function to Count If Cell Contains Text Including a Specific Text in Excel**

Up till now, we have counted the number of cells that contain text addresses, that is, **Email** Addresses.

You can also use the **COUNTIF function** to count the number of cells that contain text values including a specific text.

For example, let’s try to count the number of customers who use **Gmail** Addresses.

To accomplish that, we have to count the number of cells that include the string **“gmail”** within them.

Simple. Just wrap the text **“gmail”** within an **Asterisk symbol (*)** within the **COUNTIF function** as **criteria**.

The formula will be:

`=COUNTIF(C4:C13,"*gmail*")`

**⧪ Notes:**

- Here
**C4:C13**is the range of my contact addresses. - And
**“gmail”**is the specific text we are looking for. - You insert these according to your needs.

Look, it has successfully counted the total number of cells that contain **Gmail**** Addresses**.

And it is **4**.

**⧪ Explanation of the Formula:**

- Here the criteria of the
**COUNTIF function**is**“*gmail*”**. It counts all the strings with the text value**“gmail”**in it. - Therefore,
**COUNTIF(C4:C13,”*gmail*”)**counts all the cells within the range**C4:C13**that contain the text**“gmail”**.

**4. Use COUNTIFS Function to Count If Cell Contains Text Excluding a Specific Text in Excel**

In the previous section, we tried to count the number of cells that contain text values including a specific text.

We can also count the number of cells that contain text values excluding a specific text.

You have to use the **COUNTIFS function** instead of the **COUNTIF ****function** for that.

For example, let’s try to count the number of the cells that contain **Email** Addresses, but not **Gmail** Addresses.

Here we’ve to count the number of cells that contain text values excluding the string **“Gmail”**.

Select any suitable cell and enter this formula:

`=COUNTIFS(C4:C13,"*",C4:C13,"<>*gmail*")`

**⧪ Notes:**

- Here
**C4:C13**is the range of my contact addresses. - And
**“gmail”**is the specific text we want to exclude. - You insert these according to your needs.

Look, we have again successfully counted the number of **Email** addresses that aren’t **Gmail** addresses.

It is **3**.

**⧪ Explanation of the Formula:**

- The
**COUNTIFS****function**first counts all the cells that maintain the first criterion. - Then it counts all the cells that maintain the second criterion, and so on.
- Here it first counts all the cells within the range
**C4:C13**that are text values (**“*”**). - Then it again counts all the cells that don’t include the text
**“gmail”**(**“<>*gmail*”****).**Here**“<>*gmail*”**denotes**Not equal to “*gmail*****”**. - Thus, the formula counts all the cells that contain text values, but excluding
**“gmail”**.

**5. Run a VBA Code to Accomplish All the Tasks Simultaneously**

Up till now, we have executed four tasks separately:

- Count the Number of Cells that Contain Texts
- Then Count the Number of Cells that don’t Contain Texts
- Count the Number of Cells that Contain Texts but Include a Specific Text
- Also Count the Number of Cells that Contain Texts but Exclude a Specific Text

Now, we will develop a **Macro** using a **VBA code** that can perform all four tasks simultaneously.

**⧪ Step 1:**

**➤ **Press **ALT+F11** on your keyboard. The **VBA ****window** will open.

**⧪ Step 2:**

**➤ **Go to the **Insert** tab in the **VBA** window.** **

**➤ **From the options available, select **Module**.

**⧪ Step 3:**

**➤ **A new module window called **“Module 1”** will open.** **

**➤ **Insert the following **VBA**** code** in the module.

**⧪ Code:**

```
Sub Count_If_Cell_Contains_Text()
Dim Count As Integer
Count = 0
Task = Int(InputBox("Enter 1 to Count Cells That Contain Texts: " + vbNewLine + "Enter 2 to Count Cells That don't Contain Texts: " + vbNewLine + "Enter 3 to Count Texts That Include a Specific Text: " + vbNewLine + "Enter 4 to Count Texts That Exclude a Specific Text: "))
If Task = 1 Then
For i = 1 To Selection.Rows.Count
If VarType(Selection.Cells(i, 1)) = 8 Then
Count = Count + 1
End If
Next i
MsgBox Count
ElseIf Task = 2 Then
For i = 1 To Selection.Rows.Count
If VarType(Selection.Cells(i, 1)) <> 8 Then
Count = Count + 1
End If
Next i
MsgBox Count
ElseIf Task = 3 Then
Text = LCase(InputBox("Enter the Text That You Want to Include: "))
For i = 1 To Selection.Rows.Count
If VarType(Selection.Cells(i, 1)) = 8 Then
For j = 1 To Len(Selection.Cells(i, 1))
If LCase(Mid(Selection.Cells(i, 1), j, Len(Text))) = Text Then
Count = Count + 1
Exit For
End If
Next j
End If
Next i
MsgBox Count
ElseIf Task = 4 Then
Text = LCase(InputBox("Enter the Text That You Want to Exclude: "))
For i = 1 To Selection.Rows.Count
If VarType(Selection.Cells(i, 1)) = 8 Then
Dim Exclude As Integer
Exclude = 0
For j = 1 To Len(Selection.Cells(i, 1))
If LCase(Mid(Selection.Cells(i, 1), j, Len(Text))) = Text Then
Exclude = Exclude + 1
Exit For
End If
Next j
If Exclude = 0 Then
Count = Count + 1
End If
End If
Next i
MsgBox Count
Else
MsgBox "Please Enter an Integer between 1 to 4."
End If
End Sub
```

**⧪ Notes:**

- This code produces a
**Macro**called**Count_If_Cell_Contains_Text**.

**⧪ Step 4:**

**➤ **Save the workbook as **Excel Macro-Enabled Workbook**.

**⧪ Step 5:**

➤ Return to your worksheet.

➤ Select the range of cells in your data set where you want to count texts.

**⧪ Step 6:**

➤ Then press **ALT+F8** on your keyboard.

➤ A dialogue box called **Macro** will open. Select **Count_If_Cell_Contains_Text**** (**The name of the **Macro****)** and click on **Run**.

**⧪ Step 7:**

**➤ **An **Input Box **will appear asking you to enter an integer between 1 to 4, each for a specific task mentioned there.

**⧪ Step 8:**

➤ If you want to count cells that contain text values, enter **1**. Then click **OK**.

➤ You will get a message box showing you the number of cells that contain texts (**7** in this example).

**⧪ Step 9:**

➤ If you want to count cells that don’t contain text values, enter **2**. Then click **OK**.

➤ You will get a message box showing you the number of cells that don’t contain texts (**3** in this example).

**⧪ Step 10:**

➤ If you want to count cells that contain text values, but include a specific text, enter **3**. Then click **OK**.

➤ You will get another **Input Box** asking you to enter the specific text. Here I have entered **“gmail”**.

**⧪ Note:** This is case-insensitive. That means, if you enter **“Gmail”**, it will also include **“gmail”**.

➤ Then click **OK**.

➤ You will get a message box showing you the number of cells that contain texts, but include the specific text (**“gmail”** here,** 4**).

**⧪ Step 11:**

➤ If you want to count cells that contain text values, but exclude a specific text, enter **4**. Then click **OK**.

➤ You will get another **Input Box** asking you to enter the specific text. Here I have again entered **“gmail”**.

**⧪ Note:** This is also case-insensitive. That means, if you enter **“Gmail”**, it will also work fine.

➤ Then click **OK**.

➤ You will get a message box showing you the number of cells that contain texts, but exclude the specific text (**“gmail”** here, **3**).

**Conclusion**

Using these methods, you can count if a cell contains text in Excel, along with including or excluding a specific text. Do you know any other method? Or do you have any questions? Feel free to ask us.

**Related Articles**

**How to Count If Cell Contains Number (Easiest 7 Ways)**

**How to Count Odd and Even Numbers in Excel (3 Easy Ways)**

**Count Specific Characters in Excel Cell (4 Quick Ways)**

**How to Count Rows with Data in Excel (4 Formulas)**