Skip to main content

Count number of cells that contain specific text in Excel

Author: Xiaoyang Last Modified: 2021-09-14

Supposing, you have a list of text strings, and may want to find the number of cells that contain certain text as part of their contents. In this case, you can use the wildcard characters (*) that represents any texts or characters in your criteria when applying the COUNTIF function. This article, I will introduce how to use formulas for dealing with this job in Excel.


Count number of cells that contain specific text without case sensitive

To count the number of cells that match part of a text string, the COUNIF function in Excel can help you, please do as follows:

Generic formula with hardcoded text:

= COUNTIF ( range, "*text*" )
  • range: The range of cells contain the text strings you want to count;
  • *: The wildcard which finds any number of characters;
  • text: The specific text or character to look for.

As below screenshot shown, I want to count the number of cells that contain the certain text “Tokyo” from the range A2:A11.

Please enter or copy the below formula into a blank cell, and then press Enter key to return the result, see screenshot:

=COUNTIF(A2:A11,"*Tokyo*")


Generic formula with cell reference:

= COUNTIF ( range, "*" & cell & "*" )
  • range: The range of cells contain the text strings you want to count;
  • *: The wildcard which finds any number of characters;
  • cell: The cell contains the specific text or character to look for.

To make the formula dynamically, replace the hardcoded string with cell reference. For example, the criteria texts are placed into cell C2:C4, to count the cells contain part of these texts, please do as this.

1. Enter or copy the below formula into a blank cell where to output the result:

=COUNTIF($A$2:$A$11, "*"&C2&"*")

2. Then drag the fill handle down to the cells that you want to apply this formula, and you will get the results as below screenshot shown:

Tips: If you want to calculate the number of cells which contain more than one text criterion, for example, to count the cells that contain the item “Tokyo” and “Class1” in range A2: A11 as below screenshot shown:

To count cells with multiple substrings, please apply one of the following formulas as you want:

=COUNTIFS(A2:A11,"*Tokyo*", A2:A11,"*Class1*")
=COUNTIFS(A2:A11,"*"&C2&"*", A2:A11,"*"&C3&"*")


Count number of cells that contain specific text with case sensitive

To count the number of cells that contain specific text with case sensitive which exactly match the case text in specified cells, you should combine the SUMPRODUCT, ISNUMBER and the FIND functions to get it done, the generic syntax is:

=SUMPRODUCT(--(ISNUMBER(FIND("text", range))))
  • text: The text or cell contain the text that you are looking for;
  • range: The range of cells contain the text strings you want to count.

Please enter or copy the below formula into a blank cell, and then press Enter key to get the result:

=SUMPRODUCT(--(ISNUMBER(FIND(C2, A2:A11))))


Explanation of the formula:
  • FIND(C2, A2:A11): The FIND function is case-sensitive, it searches for the specific text in each cell of the range. If the text exists, it will return the position of the first character, otherwise the #VALUE! Error is returned. So, you will get the array like this: {7;#VALUE!;#VALUE!;#VALUE!;1;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!}.
  • ISNUMBER(FIND(C2, A2:A11)): This ISNUMBER function will convert the numbers to TRUEs, and error values to FALSEs as this: {TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}.
  • --(ISNUMBER(FIND(C2, A2:A11))): The double negative sign (--) is used to convert TRUE and FALSE values into 1 and 0 numbers as this: {1;0;0;0;1;1;0;0;0;0}.
  • SUMPRODUCT(--(ISNUMBER(FIND(C2, A2:A11))))=SUMPRODUCT({1;0;0;0;1;1;0;0;0;0}): At last, this SUMPRODUCT function sums all numbers in the array to get the final result: 3.

Relative function used:

  • COUNTIF:
  • The COUNTIF function is a statistical function in Excel which is used to count the number of cells that meet a criterion.
  • SUMPRODUCT:
  • The SUMPRODUCT function can be used to multiply two or more columns or arrays together, and then get the sum of products.
  • ISNUMBER:
  • The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not.
  • FIND:
  • The FIND function is used to find a string within another string, and returns the starting position of the string inside another one.

More articles:

  • Count number of cells not equal to many values in Excel
  • In Excel, you may easily get the number of cells not equal to a specific value by using the COUNTIF function, but have you ever tried to count the cells which are not equal to many values? For example, I want to get the total number of the products in column A but exclude the specific items in C4:C6 as below screenshot shown. This article, I will introduce some formulas for solving this job in Excel.
  • Count Number Of Cells That Contain Odd Or Even Numbers
  • As we all known, odd numbers have a remainder of 1 when divided by 2, and the even numbers have a remainder of 0 when divided by 2. This tutorial, I will talk about how to get the number of cells that contain odd or even numbers in Excel.
  • Count Number Of Cells That Contain Specific Number Of Characters
  • If you want to count the number of cells that contain a certain number of characters (such as 5 characters) in a range of cells as below screenshot shown. In Excel, the COUNTIF function can do you a favor. In this article, I will talk about how to get number of cells that contain exactly x characters or cells that contain more than a certain number of characters.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 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 VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy 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 Columns to Select Same & Different Cells ...
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 Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations