It is not impossible that we might face the necessity to count how many cells contain text within a specific region. In this article, I will try to explain 5 smart ways to **count if** **a cell contains text in Excel**. I hope it will be very helpful for you if you are looking for an efficient way to do so.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**5 Smart Ways to Count If Cell Contains Text in Excel**

In order to count if a cell contains text in Excel, I am going to apply the **COUNTIF**, **ISTEXT**, **SUMPRODUCT**, **COUNTIFS **functions, and a **VBA **code. For more clarification, I will use a dataset with the **Names** of some customers and their **Contact Addresses** of a company. Follow the following sections to have a clear idea.

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

We can use **the** **COUNTIF function** of **Microsoft ****Excel** to count the number of cells that contain text. To accomplish that, we may use an **Asterisk Symbol (*)** as the **criteria** of **the** **COUNTIF ****function**.

__Steps:__

- Select a cell (i.e.
**C16**) and insert the following formula in that cell and press**ENTER**to count the number of cells that contain text.

`=COUNTIF(C5:C14,"*")`

- Here
**C5:C14**is the range of my contact addresses. You can insert it according to your need.

**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**. Let’s dive into the details.

__Steps:__

- First of all, pick a cell (i.e.
**C16**) and apply the formula in that cell

`=SUMPRODUCT(--ISTEXT(C5:C14))`

- Here
**C5:C14**is the range of my contact addresses. You can insert it according to your need.

**⧪ Formula Breakdown ⧪**

**ISTEXT(C5:C14)** **—> **checks each cell of the range **C5:C14** and returns a **TRUE** if the cell contains a text. Otherwise, it returns **FALSE**.**Output:** *{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE}*

**–ISTEXT(C5:C14) —> **converts the array returned by **the** **ISTEXT function** into an array of 1’s and 0’s.**Output:** *{1;0;1;1;1;0;0;1;1;1}*

**SUMPRODUCT(–ISTEXT(C5:C14))**

**SUMPRODUCT({1;0;1;1;1;0;0;1;1;1})**

**—>**returns the sum of the total range.

**Output:**

*7*

**3. Use COUNTIF Function to Count If Cell Contains Specific Text**

Up till now, we have counted the number of cells that contain text addresses, that is, **Email** Addresses. We can also use the **COUNTIF function** to **count the number of cells that contain text values including a specific text.** Go through the following section for more description.

__Steps:__

- Based on your choice, select a cell (i.e.
**C16**) and input the following formula with specific text in that cell

`=COUNTIF(C5:C14,"*gmail*")`

- Here
**C5:C14**is the range of my contact addresses and**gmail**is the specific text we are looking for. You can insert the text according to your needs.

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

**4. Apply COUNTIFS Function to Count If Cell Contains Text Excluding a Specific Text**

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. We have to use **the** **COUNTIFS function** instead of **the** **COUNTIF ****function** for that.

__Steps:__

- Just insert the following formula in your preferred cell (i.e.
**C16**) to count the number of cells that contain text excluding the specific one.

`=COUNTIFS(C5:C14,"*",C5:C14,"<>*gmail*")`

- Here,
**C5:C14**is the range of my contact addresses and**gmail**is the specific text we want to exclude. You can insert the text according to your needs.

**5. Assign VBA Code to Count If Cell Contains Text**

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. Just follow the following steps carefully.

__Steps:__

- As the first step, select all the cells that you want to check.
- Next, go to the
**Developer**tab. - Click on
**Visual Basic**from the ribbon.

- Alternatively, press
**ALT+F11**on your keyboard. The**VBA****window**will open. - Go to the
**Insert**tab in the**VBA**window. - From the options available, select
**Module**.

**Module 1**will open.**VBA****code**in the module.

```
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
```

- Press
**F5**or click on the**Run**button to execute the code. - An
**Input Box**will appear asking you to enter an integer between**1**to**4**, each for a specific task mentioned there. - 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).

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

- 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**. Then, click on**OK**. - You will get a message box showing you the number of cells that contain texts, but include the specific text (
**“gmail”**here,**4**).

- 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**and click on**OK**.

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

*Note***Gmail**, it will also work fine.

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

**Conclusion**

At the end of this article, I like to add that I have tried to explain 5 smart ways to **count if** **a cell contains text in Excel**. It will be a great pleasure for me if this article helps any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using **Excel**.