Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Count if cell contains text or part of text with formula

Count if cell contains text or part of text with Kutools for Excel


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 qualified cells will be selected automatically. Download the full feature 60-day free trail of Kutools for Excel now!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!


Count if cell contains text or part of text with formula

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.


Count if cell contains text or part of text with Kutools for Excel

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:



(1) Select Cell option in the Selection type section;

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


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel

Demo: Count if cell contains text or part of text with Kutools for Excel



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Brian · 23 days ago
    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'.
  • To post as a guest, your comment is unpublished.
    Renee · 8 months ago
    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
  • To post as a guest, your comment is unpublished.
    Tresa · 10 months ago
    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?
    • To post as a guest, your comment is unpublished.
      Honza · 10 months ago
      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).
  • To post as a guest, your comment is unpublished.
    No · 11 months ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    ravikumar · 1 years ago
    Dear 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.
      crystal · 10 months ago
      Dear 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: zxm@addin99.com or post your question to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.