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


Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

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...
Tutorial Details
Excel Function: COUNTIF()
Downloadable Files: Excel File
Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)