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?

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.


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!


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.

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


Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
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.
    José Luna López · 6 days ago
    EXCELENTE MUCHAS GRACIAS!!!! ME SIRVIÓ DE MUCHO!!!!
  • To post as a guest, your comment is unpublished.
    Bala · 4 months ago
    Want to countif

    How many apple text in row2 when row 3 is apple farm
  • To post as a guest, your comment is unpublished.
    bubie · 4 months ago
    hi tolong. macam mana ni?
  • To post as a guest, your comment is unpublished.
    Brian · 6 months 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.
      crystal · 5 months ago
      Hi Brian,
      Sorry can't help you with that yet.
  • To post as a guest, your comment is unpublished.
    Renee · 1 years 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