How to count if cell contains text or part of text in Excel?
Sometimes you need to count the number of cells if they contain specific text or part of text in Excel. Here we will show you methods to achieve this.
Recommended Productivity Tools for Excel/Office
Supposing you have the below data, and want to count the number of cells which contain the text Apple.
You can use the COUNTIF function to count the number of specific text or part of text cells.
1. Select a blank cell (Cell C2), enter the formula =COUNTIF(A2:A6,"*Apple*") into the cell and then press the Enter key. You will get the result immediately in the selected cell.
Note: You can change the reference cell and the criteria in the formula as you need.
Besides the above formula, you can use add-in to solve this problem. The Select Specific Cells utility of Kutools for Excel can quickly count if cell contains text or part of text. Please do as follows.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
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:
(2) In the Specific type section, select Contains in the drop-down list, enter Apple in the blank box;
(3). Click the OK button.
Now a new Select Specific Cells dialog box pops up to tell you how many cells contain the text “Apple”. And these cells are selected immediately.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
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.· 23 days 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.· 8 months 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
To post as a guest, your comment is unpublished.· 10 months agoIs 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?
To post as a guest, your comment is unpublished.· 11 months agoThank you!
To post as a guest, your comment is unpublished.· 1 years agoDear Crystal ,
Please find this Excel ..
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.
thanks a lot for your quick reply.
To post as a guest, your comment is unpublished.· 10 months agoDear ravikumar,
Sorry for reply so late. I still can't find your uploaded file. Is there something wrong while uploading?
You can send it to my email address: firstname.lastname@example.org or post your question to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.