How to Use COUNTIF Function to Count Text from List in Excel

While working in an Excel worksheet, you may need to count if cells contain text from a list in Excel. To solve this problem, we can use five easy methods. In this article, I will show you those five methods to Excel countif cell contains text from list. I will show the necessary pictures to ensure your better understanding. Hopefully, you will enjoy the whole procedure.

How to Use COUNTIF Function to Count Text from List in Excel

From here, I will show you how to count if a cell contain text from list in excel. Here, I will consider a dataset about the Sales Report of ABC Agro. the dataset contains three columns, B, C, and D called Year, Product, and Sales respectively. The dataset ranges from B4 to D10. The dataset has six rows here. With this dataset, I will show you how to find the excel countif contains text from list.

1. Use COUNTIF Function to Count Cells that Contain Text in Excel

This is the first method of this article. Here we will use the COUNTIF function to count if a cell contains text from list in excel. Please follow the process step by step. The pictures will help you to understand the steps more clearly.

Steps:

Select the D12 cell first.

Then, write the following formula in the selected cell.

=COUNTIF(C5:C10,"*")

Hence, press enter.

As a result, you will find result 6 in the picture given below.

2. Count Cells Contains Text Including a Specific Text by Combining ISTEXT and SUMPRODUCT Functions

In this portion of this article, I will show you another process to count if a contains text in excel. Here, I will combine ISTEXT and SUMPRODUCT functions. Let’s follow the procedure step by step.

Steps:

First, select the D12 cell.

After that, write down the following formula in the selected cell.

=SUMPRODUCT(--ISTEXT(C5:C10))

After pressing enter, you will get the result just like the picture given below.

🔎 How Does the Formula Work?

ISTEXT(C5:C10) checks each cell of the range C5:C10 and returns a TRUE if the cell contains a text. Otherwise, it returns a FALSE.
Thus ISTEXT(C5:C10) returns an array of boolean values, TRUE and FALSE.
–ISTEXT(C5:C10) 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 Cells that Contains Text Including a Specific Text in Excel

This is the third method of this article. This is one of the most common methods of all the methods mentioned in this article. I will use the COUNTIF function here to countif a cell contains text from list in excel. Follow the steps given below.

Steps:

Firstly, select the D12 cell.

Then, copy the following formula in the D12 cell.

=COUNTIF(C5:C10,"*apple*")

Hence, press Enter.
As a consequence, you will find the following result.

4. Utilize COUNTIFS Function to Count Cells that Contain Text Excluding a Specific Text in Excel

This is the fourth method of this article. This method is as similar to the third method. But here I will use the COUNTIFS function. Let’s follow the procedure, step by step, and bring out the answer.

Steps:

Select the D12 cell.

After that, copy the following formula in the selected cell.

=COUNTIFS(C5:C10,"mango")

Then, press the Enter key.
As a consequence, you will find the result just like the picture given below.

5. Apply VBA Code

This is the last method of this article. But this is the most innovative method to count if a cell contains text from list in Excel. Follow the procedure step by step.

Steps:

Press Alt + F11 on your keyboard. The VBA window will open.

Then, go to the Insert tab in the VBA window.
After that, select Module.

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

Then, select the cells from C5 to C10 cells.

Then, press Alt + F8 on your keyboard.
Select the option shown below.
After that, select the run button.

As a result, the following window will appear.

Hence, write the option 3 in the box.

Enter the word ‘apple’ in the next window.

After pressing the OK button, you will get the result in the following window.

Count If a Cell Contains a Number

In this part of this article, I will show you an easy process to count if a cell contains a number in Excel. I will use the same dataset with a slight change in the sales column. Follow the procedure steps given below.

Steps:

Select the D12 cell first.

Then, copy the following formula in the selected cell.

=COUNT(D5:D10)

Hence, press Enter.

Consequently, you will find the result in the D12 cell.

Count If Cell Contains Multiple Text

This is the last topic of this article. I will count if the cells here contain multiple texts. Follow the following steps here. I have attached the necessary images for your convenience.

Steps:

After selecting the D12 cell, copy the following formula in the selected cell.

=COUNTIF(C5:C10, "mango")+COUNTIF(B5:B10, "3")

After pressing enter, you will find the result given below.

Things to Remember

You need to save the Excel worksheet by enabling VBA macros.

Download Practice Workbook

Please download the workbook to practice yourself.

Countif Contains Text From List.xlsm

Conclusion

In this article, I have tried to explain how to find Excel countif contains text from list. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. I hope you have enjoyed the whole tutorial. If you have any queries, please ask me in the comment section. Don’t forget to give us your feedback.

<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!