by Svetlana Cheusheva, updated on
The tutorial shows how to count number of cells with certain text in Excel. You will find formula examples for exact match, partial match and filtered cells.
Last week we looked at how to count cells with text in Excel, meaning all cells with any text. When analyzing large chunks of information, you may also want to know how many cells contain specific text. This tutorial explains how to do it in a simple way.
Microsoft Excel has a special function to conditionally count cells, the COUNTIF function. All you have to do is to supply the target text string in the criteria argument.
Here's a generic Excel formula to count number of cells containing specific text:
The following example shows it in action. Supposing, you have a list of item IDs in A2:A10 and you want to count the number of cells with a particular id, say "AA-01". Type this string in the second argument, and you will get this simple formula:
=COUNTIF(A2:A10, "AA-01")
To enable your users to count cells with any given text without the need to modify the formula, input the text in a predefined cell, say D1, and supply the cell reference:
=COUNTIF(A2:A10, D1)
Note. The Excel COUNTIF function is case-insensitive, meaning it does not differentiate letter case. To treat uppercase and lowercase characters differently, use this case-sensitive formula.
The formula discussed in the previous example matches the criteria exactly. If there is at least one different character in a cell, for instance an extra space in the end, that won't be an exact match and such a cell won't be counted.
To find the number of cells that contain certain text as part of their contents, use wildcard characters in your criteria, namely an asterisk (*) that represents any sequence or characters. Depending on your goal, a formula can look like one of the following.
Count cells that contain specific text at the very start:
Count cells that contain certain text in any position:
For example, to find how many cells in the range A2:A10 begin with "AA", use this formula:
=COUNTIF(A2:A10, "AA*")
To get the count of cells containing "AA" in any position, use this one:
=COUNTIF(A2:A10, "*AA*")
To make the formulas more dynamic, replace the hardcoded strings with cell references.
To count cells that begin with certain text:
=COUNTIF(A2:A10, D1&"*")
To count cells with certain text anywhere in them:
=COUNTIF(A2:A10, "*"&D1&"*")
The screenshot below shows the results:
In situation when you need to differentiate uppercase and lowercase characters, the COUNTIF function won't work. Depending on whether you are looking for an exact or partial match, you will have to build a different formula.
To count the number of cells with certain text recognizing the text case, we will use a combination of the SUMPRODUCT and EXACT functions:
How this formula works:
For example, to get the number of cells in A2:A10 that contain the text in D1 and handle uppercase and lowercase as different characters, use this formula:
=SUMPRODUCT(--EXACT(D1, A2:A10))
To build a case-sensitive formula that can find a text string of interest anywhere in a cell, we are using 3 different functions:
How this formula works:
To test the formula on real-life data, let's find how many cells in A2:A10 contain the substring input in D1:
=SUMPRODUCT(--(ISNUMBER(FIND(D1, A2:A10))))
And this returns a count of 3 (cells A2, A3 and A6):
To count visible items in a filtered list, you will need to use a combination of 4 or more functions depending on whether you want an exact or partial match. To make the examples easier to follow, let's take a quick look at the source data first.
Assuming, you have a table with Order IDs in column B and Quantity in column C like shown in the image below. For the moment, you are interested only in quantities greater than 1 and you filtered your table accordingly. The question is – how do you count filtered cells with a particular id?
To count filtered cells whose contents match the sample text string exactly, use one of the following formulas:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(B2:B10=F1))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(B2:B10=F1))
Where F1 is the sample text and B2:B10 are the cells to count.
How these formulas work:
At the core of both formulas, you perform 2 checks:
Finally, the SUMPRODUCT function multiplies the elements of the two arrays in the same positions, and then sums the resulting array. Because multiplying by zero gives zero, only the cells that have 1 in both arrays have 1 in the final array. The sum of 1's is the number of filtered cells that contain the specified text.
To count filtered cells containing certain text as part of the cell contents, modify the above formulas in the following way. Instead of comparing the sample text against the range of cells, search for the target text by using ISNUMBER and FIND as explained in one of the previous examples:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISNUMBER(FIND(F1, B2:B10))))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(ISNUMBER(FIND(F1, B2:B10))))
As the result, the formulas will locate a given text string in any position in a cell:
Note. The SUBTOTAL function with 103 in the function_num argument, identifies all hidden cells, filtered out and hidden manually. As the result, the above formulas count only visible cells regardless of how invisible cells were hidden. To exclude only filtered out cells but include the ones hidden manually, use 3 for function_num.
That's how to count the number of cells with certain text in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents