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.

**Table of Contents**hide

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

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

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

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

**Similar Readings**

**How to Use COUNTIF Function in Excel Greater Than Percentage****Use COUNTIF to Count Date Less Than Today in Excel****How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)****Use COUNTIF with WEEKDAY in Excel (2 Easy Methods)****How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)**

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

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

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

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

**Similar Readings**

**Count Blank Cells with Excel COUNTIF Function: 2 Examples****COUNTIF Between Two Dates in Excel (4 Suitable Examples)****Excel COUNTIF with Greater Than and Less Than Criteria****How to Apply COUNTIF Between Two Cell Values in Excel****Use Excel COUNTIF Function to Count Cells Greater Than 0**

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

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

**Calculate Frequency Using COUNTIF Function in Excel****How to Use Nested IF and COUNTIF Formula in Excel****[Fixed] COUNTIF Function with Wildcard Not Working in Excel****COUNTIF Function to Count Cells That Are Not Equal to Zero****How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)****Apply Excel COUNTIF with Pivot Table Calculated Field****Difference Between SUMIF and COUNTIF Functions in Excel**