How to count if cell contains text or part of text in Excel?
Supposing you have the below data, and want to count the number of cells which contain the text "Apple", number of cells contain the text "Orange" and cells contain "Peach" separately, how to get it done? In this tutorial, we explain the COUNTIF function in detail to help you quickly count the number of cells if they contain text or part of specific text in Excel. Besides, we introduce an awesome feature to acheive it easily with only clicks.
Count cells contain text with the COUNTIF function
Count if cell contains text or part of text with an awesome feature
Count if cell contains text or part of text with the COUNTIF function
The COUNTIF function can help to count cells that contain part of text in a range of cells in Excel. Please do as follows.
1. Select a blank cell (such as E5), copy the below formula into it and then press the Enter key. And then drag the Fill Handle down to get all results.
=COUNTIF(B5:B10,"*"&D5&"*")
Syntax
=COUNTIF (range, criteria)
Arguments
- Range (required): The range of cells you want to count.
- Criteria (required): A number, expression, cell reference, or text string that determines which cells will be counted.
Notes:
- In the formula, B5:B10 is the range of cells you want to count. D5 is the cell reference containing what you want to look for. You can change the reference cell and the criteria in the formula as you need.
- If you want to directly typing the text in the formula to count, please apply the below formula:
=COUNTIF(B5:B10,"*Apple*") - This formula is case-insensitive.
Only several clicks can count if cell contains text or part of text in Excel:
The Select Specific Cells utility of Kutools for Excel can help you quickly count number of cells in a range if they contain certain text or part of text. After getting the result in a popping up dialog box, all matched cells will be selected automatically. Click for details.
Download Kutools for Excel now! (30-day free trail)
Count cells contain text with the COUNTIF function
As the below screenshot shown, if you want to count the number of cells in a certain range that only contain text, method in this section can help you.
1. Select a blank cell to output the result, copy the below formula into it and press the Enter key.
=COUNTIF(B5:B10,"*")
Count if cell contains text or part of text with Kutools for Excel
Tip: Besides the above formula, here introduce an awesome feature to easily solve this problem. With the Select Specific Cells utility of Kutools for Excel, you can quickly count if cell contains text or part of text with clicks. You can even countif with OR or And condition as you need with this feature. Please do as follows.
Before using the Kutools for Excel, you need to take minutes to free download and install it firstly.
1. Select the range you want to count number of cells containing specific text.
2. Click Kutools > Select > Select Specific Cells.
3. In the Select Specific Cells dialog box, you need to:
- Select Cell option in the Selection type section;
- In the Specific type section, select Contains in the drop-down list, enter Apple in the text box;
- Click the OK button.
- Then a prompt box pops up to tell you how many cells matching the condition. Click the OK button and all qualified cells are selected at the same time.
Tip. If you want to have a free trial (60-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Related articles
Use countif with multiple criteria in Excel
In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. But sometimes, we need to use multiple criteria for counting, this will be more complex. This tutorial will show you methods to achieve it.
Click to know more...
Count if cells are start with or end with specific text in Excel
Supposing you have a range of data and you want to count the number of cells which start with “kte” or end with “kte” in a worksheet. This article introduces some tricks instead of manual counting for you.
Click to know more...
Countif a specific value across multiple worksheets
How could you count a specific values across multiple worksheet? Such as get the number of occurrence of a specific value “Excel” from multiple worksheets. This tutorial introduces methods to get it done.
Click to know more...
Demo: Count if cell contains text or part of text with Kutools for Excel
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
