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

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

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

### Office Tab - brings tabbed interface to Office, and make your work much easier

• 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!
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hi in a list having MD546FG7586
MD6478BD777
MD6836GHF77
How do i count only that containing MD
This comment was minimized by the moderator on the site
Excelente aporte, felicitaciones.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Did someone check this on Macintosh? Im struggeling in Excel365, and cant get a substring to count. Something with using the asteric on the Mac? That blows!
This comment was minimized by the moderator on the site
EXCELENTE MUCHAS GRACIAS!!!! ME SIRVIÓ DE MUCHO!!!!
This comment was minimized by the moderator on the site
Want to countif

How many apple text in row2 when row 3 is apple farm
This comment was minimized by the moderator on the site
hi tolong. macam mana ni?
This comment was minimized by the moderator on the site
Okay, what if I want to do a countif on a specific position in the cell. I'm looking for an 'r' in the third character following a '-' in the cell. So in English count only cells where the third character following the dash ('-') is an 'r'.
This comment was minimized by the moderator on the site
Hi Brian,
This comment was minimized by the moderator on the site
Hi,
needing help please, i need this to do count over 33 work sheets with the same name. this is what i tried to start with =COUNTIF( "1 - 33'!,"*Bosch MIC 7000i IP7230 1080p*") - but no go. the 1 - 33 is the number range of spreadsheet names.

How ever this is where it gets trickier.
i need it to also include the totals in the individual spread sheet - name of product / part number and supplier....
so simple hahahaha
This comment was minimized by the moderator on the site
Hi Renee,
Thank you for sharing it.
This comment was minimized by the moderator on the site
Is there a way to use the COUNTIF function but rather than typing in the word to count, the formula can look at another cell where the user could type the name into the cell and the cell next to it (with the COUNTIF function) will show how many time the typed in date is in the worksheet?
This comment was minimized by the moderator on the site
Yes, I've just used it. Assuming the cell where you type this word is C1, the syntax would be following: =COUNTIF(A2:A6,"*"&C1&"*"). Also works with SUMIF, you just add the third condition (sum range).
This comment was minimized by the moderator on the site
Thank you!
This comment was minimized by the moderator on the site
Dear Crystal ,

i cannot solve this ...

My Problem is i want to show yes or no for column J reached 80 % (subtotal) other wise highlight the cell untill reached 80% of the total .and also give me the formula for the same.

This comment was minimized by the moderator on the site
Dear ravikumar,
You can send it to my email address: or post your question to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
This comment was minimized by the moderator on the site
please find this picture and tell me the formula for count by name but remember vin is same or multiple entries done in the same vin ..so please tell me i want to count how many vin my no is +91 8655315561 or whats app me its urgent
This comment was minimized by the moderator on the site
Good Day,
This comment was minimized by the moderator on the site
in the column A employee name is written and in column B car number is written and c column accessories amount is written ..etc

i want count a how many car is billed with accessories in front of their name ...

i want formula for count function i tried many function but unable to find it ...so please give me solution for this.
This comment was minimized by the moderator on the site
The formula only seems to work if the word I'm counting is the FIRST word in the cell. How do I count the number of cells that CONTAIN that word, whetheror not it's at the beginning of the cell?
This comment was minimized by the moderator on the site
Dear JoAnn,
Thank you for your comment. The formula =COUNTIF(A2:A6,"*Apple*") can count the number of cells that contain the word Apple no matter which position it loactes in the cell. Please don't forget to enbrace the word with asterisks.
This comment was minimized by the moderator on the site
Hi,

I have want to count the number of cells which has 2 specific words.

Example, I have cells which may contain up to 6 words separated by a comma.

Example:

A1: banana,apple,orange
A2: apple,orange,pineapple
A3: pineapple,banana,guava
A4: apple,banana,guava
A5: orange,guava,pineapple
A6: apple,guava,orange

I want to count how many cells contain the words Apple AND Orange.

In the above case, count would be 3

K
This comment was minimized by the moderator on the site
Good Day,
As the problem you mentioned, please try this array formula =SUM(--(IF((LOWER(LEFT(TRIM(A1:A6),LEN("apple")+1))="apple,"),TRUE,IF(LOWER(RIGHT(TRIM(A1:A6),LEN("apple")+1))=",apple",TRUE,(IF(ISERR(SEARCH(",apple,",TRIM(A1:A6))),FALSE,TRUE))))*IF((LOWER(LEFT(TRIM(A1:A6),LEN("orange")+1))="orange,"),TRUE,IF(LOWER(RIGHT(TRIM(A1:A6),LEN("orange")+1))=",orange",TRUE,(IF(ISERR(SEARCH(",orange,",TRIM(A1:A6))),FALSE,TRUE)))))) + Ctrl + Shift + Enter
This comment was minimized by the moderator on the site
i have excel sheet containing 3 columns (Section / Name / Gender)
what formula can count how many "male" or "female" in very section
This comment was minimized by the moderator on the site
Dear Moataz,
Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
This comment was minimized by the moderator on the site
I HAVE TO COUNT THE TOTAL CELL WHICH CONTAINS A NAME AND NUMBER, SO HOW TO COUNT NAMES AND NUMBERS IN EXCEL EX. CELL A1:A5 CONTAINS APPLE 2143 BALL 5431 CAT TELL ME HOW TO COUNT IT
This comment was minimized by the moderator on the site
=COUNTIF(A1:A5,"APPLE")

Sorry I am not sure I got your question. Would be nice if you could provide screenshot of what you are trying to do..
This comment was minimized by the moderator on the site
Your response doesn't quite work, you must include * around the text you're searching. ie =countif(A1:A5, "*apple*")
This comment was minimized by the moderator on the site
Your response does not work in this scenario.
This comment was minimized by the moderator on the site
Hi, Great tutorials. This is just what I have been trying to do, however with one exception. What I need to do is instead of search and count for a cell that contains apple I need it to search to see if the cells contain the contents of a another cell with wildcard. Currently using COUNTIF(I\$8:O\$30,B36) which is on the most part doing the job. Cell B36 will contain 2 or the initials, eg GP or TV, which works fine but what I need to do is count if the cells contains GP when cell b36 contains GP/TV. The formula COUNTIF(I\$8:O\$30,B36) does not then pick up and count that cell because it is not just GP. I know I could use =COUNTIF(A2:A6,"*GP*") however the cell for the code has 52 if commands in it and is repeated on 37 rows so its not ideal to use that. Can it be done? Thanks for looking
This comment was minimized by the moderator on the site
Graham, did you ever find a way to do this please? As I'm currently trying to find a way to do the exact same thing.

Thank you
This comment was minimized by the moderator on the site
If we take Graham's example, then you can write: COUNTIF(I\$8:O\$30,"*"&B36&"*").

Hope this helps :)
This comment was minimized by the moderator on the site
When using =countif(s) you can use * around the specific text you're trying to look for in a statement. For example, I was looking to count cells where they had the word "ran" in them, when they would be vastly different entries. There would be such things as "ran off road", "animal hit/ran off road", "overturn/ran off road".

So I did basically =countif(range, "*ran*") and it works fine. Hope this helps?
This comment was minimized by the moderator on the site
When using =countif(s) you can use * around the text you're trying to look for in a statement. For example, I was looking to count cells where they had the word "ran" in them, when they would be vastly different entries. There would be such things as "ran off road", "animal hit, ran off road", "overturn, ran off road".

So I did basically =countif(range, "*ran*") and it works fine. Hope this helps?
This comment was minimized by the moderator on the site
What i did before was place the this code: (=COUNTIF(A2:A6,"*GP*")) on 1 cell and another formula in another cell, then just click =SUM on the 2 cells with the code.
This comment was minimized by the moderator on the site
Try =COUNTIF(I\$8:O\$30,"="&"*"&B36&"*")
This comment was minimized by the moderator on the site
This worked for me, except I don't want it count if the cell is blank. Any suggestions?
There are no comments posted here yet