Skip to main content

Master counting cells with text in Excel: A Complete Guide

Excel is an indispensable tool for data analysis, and often you need to quantify how often certain types of data appear in your spreadsheet. Counting cells that contain text or specific text is a common task that can be accomplished with a few functions and techniques. This guide will cover the methods for counting text entries in various scenarios.


Video


Count cells that contain any text

When working with Excel, you might want to count the number of cells that contain any text. This is useful for getting a quick idea of how many cells are non-numeric or non-blank.

Here, I will count cells with any text in a range (e.g., the range A2:A14 as shown in the screenshot below) as an example.

Select a blank cell (D3 in this case), enter the following formula and press Enter to get the result.

=COUNTIF(A2:A14, "*")

The total number of cells that contain text are counted as shown in the screenshot above.

Notes:
  • You also can use the SUMPRODUCT function with the ISTEXT function to count cells with text in Excel.
    =SUMPRODUCT(--ISTEXT(A2:A14))
  • In the formulas, A2:A14 is the range where you want to count the cells that contain text.
  • In this example, you will notice that the number displayed in cell A7 is also counted as one of the text results. This is because this number was entered as text (with an apostrophe at the beginning).
  • The table below lists which cells will be counted as text cells and which will not.
    Cells that will be counted Cells that will not be counted
    • Cells with any text
    • Cells with any special characters
    • Cells with number entered as text
    • Cells with only spaces
    • Cells with an apostrophe (')
    • Cells with an empty string (="")
    • Cells with only non-printing characters
    • Cells with numbers
    • Cells with dates
    • Cells with times
    • Cells with error values caused by formulas
    • Cells with logical values (TRUE and FALSE)
    • Blank cells

Count cells with visible text only

The formula described above counts all cells containing any text and excludes blank cells. However, it may also count cells that seem empty but contain non-visible characters like spaces, apostrophes, empty strings, etc., as shown in the screenshot below.

If you aim to count only cells with text visible to the eye, the formula in this section would be more suitable.

Select a blank cell (D3 in this case), enter the following formula and press Enter to get the result.

=COUNTIFS(A2:A13,"*?*", A2:A13, "<> ")

As you can see from the screenshot above, the result "5" in cell D3 is the number of visible text cells in the range A2:A13.

Notes:
  • In the formulas, A2:A13 is the range where you want to count the cells that contain visible text.
  • Using this formula, the result will exclude cells that appear to be empty but contain non-visible characters like spaces, apostrophes, empty strings, etc.
  • To count cells with text excluding only spaces, try this formula.
    =COUNTIFS(A2:A13,"*",A2:A13,"<> ")

Count Cells that Contain Specific Text in Excel

There are instances where you'll need to be more specific in what you're counting. You might want to count cells that contain exact words or phrases, or maybe you're looking for a partial match within the cell content. The methods in this section can help you solve these problems.


Count cells with specific text (exact match)

To count cells that exactly match a specific text, for example, as shown in the screenshot below, to count cells in the range A2:A12 that fully match the text “Apple”, the formula in this section can help. Please do as follows.

Select a cell (D5 in this case), enter the following formula and press Enter to get the result.

=COUNTIF(A2:A12, D4)

As shown in the screenshot above, the number of cells that exactly match the text "Apple" has now been counted.

Notes:
  • In this formula, A2:A12 is the range where you want to count the cells that contain specific text. D4 is the cell containing the specific text you will count cells based on.
  • You can directly input your specific text into the formula if it's not pre-entered in a cell. In such instances, modify the formula to:
    =COUNTIF(A2:A12, "Apple")
  • This formula is case-insensitive, which means that if a cell contains the text "apple" or "APPLE", it will also be counted. To perform a case-sensitive count, go to the Count cells with specific text (exact match) in case-sensitive section.
  • Here I would like to recommend you a handy tool – Select Specific Cells of Kutools for Excel. This tool helps you easily count and select cells with specific text in one or multiple ranges. Simply make the following configurations to get the total number of cells that contain a specific text and select the matching cells at the same time. Try it now and get a 30-day free trial.

Count cells with specific text (partial match)

If you want to count cells that partially match a specific text, for example, as shown in the screenshot below, to count cells in the range A2:A12 that contain "Apple" anywhere within the cell, you can try the formula in this section.

Select a cell (D5 in this case), enter the following formula and press Enter to get the result.

=COUNTIF(A2:A12, "*"&D4&"*")

As shown in the screenshot above, the number of all cells that partially match the text "Apple" has now been generated.

Notes:
  • In this formula, A2:A12 is the range where you want to count the cells that contain specific text. D4 is the cell containing the specific text you will count cells based on.
  • The asterisk symbol (*) is the most general wildcard that can represent any number of characters.
  • You can directly input your specific text into the formula if it's not pre-entered in a cell. In such instances, modify the formula to:
    =COUNTIF(A2:A12, "*Apple*")
  • This formula counts the cells containing "Apple" in any position.
    • To count the number of cells that begin with "Apple", use this formula:
      =COUNTIF(A2:A12, "Apple*")
    • To count the number of cells that end with “Apple”, use this formula:
      =COUNTIF(A2:A12, "*Apple")
  • This formula is case-insensitive, which means that if a cell contains the text "apple" or "APPLE", it will also be counted. To perform a case-sensitive count, go to the Count cells with specific text (partial match) in case-sensitive section.

Count cells with specific text that is case-sensitive

Because Excel's COUNTIF function isn't case-sensitive, the formulas in the methods above don't differentiate between uppercase and lowercase letters. If you need to count cells with specific case-sensitive text, you will require different formulas.

Count cells with specific text (exact match and case-sensitive)

To count cells that exactly match the specific text while being case-sensitive, use the following formula.

=SUMPRODUCT(--EXACT(D4, A2:A12))

Note: You can directly input your specific text into the formula if it's not pre-entered in a cell.
=SUMPRODUCT(--EXACT("Apple", A2:A12))
Count cells with specific text (partial match and case-sensitive)

To count cells that partially match the specific text while being case-sensitive, use the following formula.

=SUMPRODUCT(--(ISNUMBER(FIND(D4, A2:A12))))

Note: You can directly input your specific text into the formula if it's not pre-entered in a cell.
=SUMPRODUCT(--(ISNUMBER(FIND("Apple", A2:A12))))

A Few Clicks to Count and Select Cells that Contain Specific Text

If you are looking for a simpler method to count cells with specific text, the Select Specific Cells feature of Kutools for Excel will be a good choice for you. This feature does more than just counting, it allows cells to be selected simultaneously based on specified text. The tool provides a variety of conditions, such as Equals, Begins with, Ends with, Contains, etc., and also supports matching cells based on two criteria simultaneously. This allows users to quickly count and select cells that match their provided text, which is an advantage that can not be easily achieved by the standard formulas.

After downloading and installing Kutools for Excel, select Kutools > Select > Select Specific Cells. In the opening Select Specific Cells dialog box, you need to:

  1. Select the range where you want to count the cells that contain specific text.
  2. Select the Cell option in the Selection type section.
  3. Specify a condition in the Specify type drop-down list.
  4. Enter the specific text you want to count.
  5. Click the OK button.
    In this case, as I need to count cells that exactly match the text “Apple”, I select Equals from the drop-down list and enter the text Apple in the textbox.

Result

A Kutools for Excel dialog box will then pop up showing the number of cells found and selecting them.

Notes:
  • To count cells that contain a specific text anywhere within the cell, you need to select the Contains option from the Specific type drop-down list.
  • To count cells that begin or end with a specific text, you need to select the Begins with or Ends with option from the Specific type drop-down list.
  • This feature can handle multiple ranges at the same time.
  • This feature also supports counting and selecting cells that match with two criteria simultaneously. The screenshot below shows the conditions for counting and selecting cells that not only contain the specific text "Apple" anywhere in the cell but also must end with the character "A".
  • To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.
  • To know more about this feature, please visit this page: Select specific cells, entire rows or columns based on criteria.

In conclusion, there are different methods to count cells with text or specific text in Excel. Whether you're looking for a quick fix or a comprehensive solution, it's necessary to explore and understand the various techniques to find the method that best suits your needs. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!