Count number of cells that contain specific text in Excel
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
- Count number of cells that contain specific text with case sensitive
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:
- 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:
Generic formula with cell reference:
- 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:
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,"*"&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:
- 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:
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 for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
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.
