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