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

**Read more: How to Count Cells in Excel with Different Text (5 Ways)**

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

**Read More:** **How to Count Specific Words in a Column in Excel (2 Methods)**

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

**Read More: ** **Excel VBA to Count Cells Containing Specific Text**

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