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 explains 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
Easily 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. Download the full feature 60-day free trail of Kutools for Excel now!
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.
- 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:
- This formula is case-insensitive.
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.
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.
Recommended Productivity Tools for Excel
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 days agoEXCELENTE MUCHAS GRACIAS!!!! ME SIRVIÓ DE MUCHO!!!!
To post as a guest, your comment is unpublished.· 4 months agoWant to countif
How many apple text in row2 when row 3 is apple farm
To post as a guest, your comment is unpublished.· 4 months agohi tolong. macam mana ni?
To post as a guest, your comment is unpublished.· 6 months agoOkay, 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'.
To post as a guest, your comment is unpublished.· 1 years agoHi,
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