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.


Download Practice Workbook

Please download the workbook to practice yourself.


5 Effective Ways to Use COUNTIF Function to Count 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.

dataset of excel countif 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.

COUNTIF Function to excel countif text from list

  • Then, write the following formula in the selected cell.
=COUNTIF(C5:C10,"*")
  • Hence, press enter.

COUNTIF Function to excel countif text from list

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

COUNTIF Function to excel countif text from list

Read More: Excel COUNTIF to Count Cell That Contains Text from Another Cell


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.

Istext and Sumproduct Function to excel countif text from list

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

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

Istext and Sumproduct Function to excel countif text from list

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

Istext and Sumproduct Function to excel countif text from list

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

Read More: COUNTIF Excel Example (22 Examples)


Similar Readings


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.

Countif Function to excel countif text from list

  • Then, copy the following formula in the D12 cell.
=COUNTIF(C5:C10,"*apple*")

Countif Function to excel countif text from list

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

Countif Function to excel countif text from list

Read More: Apply COUNTIF Function in Multiple Ranges for Same Criteria


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.

Countifs Function to excel countif text from list

  • After that, copy the following formula in the selected cell.
=COUNTIFS(C5:C10,"mango")

Countifs Function to excel countif text from list

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

Countifs Function to excel countif text from list

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


Similar Readings


5. Apply VBA Code

This is the last method of this article. But this is the most innovative method to coun 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.

Apply Vba code to excel countif text from list

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

Apply Vba code to excel countif text from list

  • However, insert the following VBA code in the module.

Apply Vba code to excel countif text from list

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.

Apply Vba code to excel countif text from list

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

Read More: VBA COUNTIF Function in Excel (6 Examples)


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.

Read More: How to Use COUNTIF Between Two Numbers (4 Methods)


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.

Read More: COUNTIF vs COUNTIFS in Excel (4 Examples)


Things to Remember

  • You need to save the excel worksheet by enabling VBA macros.

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. You will find such interesting blogs on our website Exceldemy.com. 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.


Related Articles

Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo