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

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.


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.

Data Set to Count If Cell Contains Text in Excel

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

COUNTIF Function to Count If Cell Contains Text in Excel

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.

SUMPRODUCT Function to Count If Cell Contains Text in Excel

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.

COUNTIFS Function to Count If Cell Contains Text in Excel

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:

  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.

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

Inserting Module to Count If Cell Contains Text in Excel

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

VBA Code to Count If Cell Contains Text in Excel

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

Selecting Cells to Count If Cell Contains Text in Excel

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

Running Macro to Count If Cell Contains Text in Excel

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

Output to Count If Cell Contains Text in Excel

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

Output to Count If Cell Contains Text in Excel

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

Output to Count If Cell Contains Text in Excel

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

Output to Count If Cell Contains Text in Excel


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)

 

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

Leave a reply

ExcelDemy
Logo