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

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.

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.

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

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

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

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

• 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: "))
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).

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.

Related Articles

• How to Count Filtered Cells with Text in Excel (3 Methods)
• How to Count Frequency of Text in Excel (Using VBA)!