# How to Count If Cell Contains Text in Excel (5 Easy Approaches)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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 to count the number of cells that contain text.
`=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 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 to count the number of cells that contain specific text.
`=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. ### 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:

1. Count the Number of Cells that Contain Texts
2. Then Count the Number of Cells that don’t Contain Texts
3. Count the Number of Cells that Contain Texts but Include a Specific Text
4. 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. •  A new module window called Module 1 will open.
•  Insert the following 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: "))
For i = 1 To Selection.Rows.Count
If VarType(Selection.Cells(i, 1)) = 8 Then
Count = Count + 1
End If
Next i
MsgBox Count
For i = 1 To Selection.Rows.Count
If VarType(Selection.Cells(i, 1)) <> 8 Then
Count = Count + 1
End If
Next i
MsgBox Count
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
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
This method is case-insensitive. That means, if you enter Gmail, it will also work fine.

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

## Related Articles #### Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  