Count Cells that Contain Specific Text in Excel

Add to Favorites
Author: | Edits: don

Simple formula to count the occurrence of any text value in a cell or range of cells in Excel.

You will learn:

  • How to count values that come at the start of a cell.
  • How to count values that come at the end of a cell.
  • How to count values that are in the middle of a cell.
  • How to count values that are located anywhere in a cell.

This simple tutorial combines the COUNTIF() function with wildcard characters.

Count Text Formula

=COUNTIF(A1:A5,"*red*")

red is the sample word to look for.

* asterisk is placed before and after the word and it means to match any character. You can place one of these before the text, one after the text, or one asterisk before and after the text.

Additional Resources

CountIf Function in Excel (microsoft resource)

Count Words in Cells in Excel (older resource) (microsoft resource)


Excel Function: COUNTIF()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Count the Number of Cells that Start or End with Specific Text in Excel
Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...
Wildcards in Excel
Tutorial: Wildcards are characters that allow you to make more robust functions, searches, and filt...
Determine if Cells Contain a Specific Value in Excel
Tutorial: Find if a cell or range of cells contains a specific value in Excel. This method can be us...
Count Cells Containing TRUE or FALSE in Excel
Tutorial: Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count...
3 Methods to Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all space...