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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

Read More: COUNTIF Excel Example (22 Examples)

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

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

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

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

Advanced Excel Exercises with Solutions PDF