Home > Formulas > Count > Text Strings Count Specific Text CodesFor cells that contain multiple text codes, count the number of cells that contain a specific code. Thanks to Leonid Koyfman for sharing his solution to this problem, that was originally published in the Contextures Excel newsletter. NOTE: For a simple formula to count specific items in a list, go to the Count Specific Items page. |
To count cell contents in Excel, based on one criterion, or multiple criteria, use the COUNTIF or COUNTIFS functions, They work efficiently, and return the correct result for most problems.
A Contextures newsletter reader asked how to count a specific text code -- "JK" in a range of cells. A simple COUNTIF wouldn't work, because there was a similar code, "JKA" that should not be counted.
In the screen shot below, there are 5 coloured cells contain "JK", alone or with other codes. There is a COUNTIF in cell C6:
=COUNTIF(B2:E3,"*" & B6 & "*")
That formula finds 7 cells with "JK", because it also counts cells E2 and C3, which contain "JKA'
Instead of using a single COUNTIF function, multiple COUNTIF functions can be combined in a single formula, with a SUM function to total the results.
In the cells, the codes are separated by a pipe character -- "|". A named range was created -- SelSep -- and it contains that character. That makes it easy to refer to the separator in the formula.
In this formula, there are 4 COUNTIF functions, and they count the following:
Those four formulas are wrapped with a SUM function, to get the total count.
A much shorter solution is to use the SEARCH function, in an array-entered formula. Thanks to Leonid Koyfman for sharing this solution.
As in the previous example, the codes are separated by a pipe character -- "|". A named range, SelSep, contains that character, and that name is used in the formulas.
In this formula, four functions are used -- SUM, IFERROR, SIGN and SEARCH. This formula is array-entered, with Ctrl+Shift+Enter
=SUM(IFERROR(SIGN( SEARCH(SelSep&$C5&SelSep, SelSep&$B$2:$E$3&SelSep)),0))
First, the search string is created, by comgining the separator, code to look for, and separator
=SelSep&$C5&SelSep
Cell C5 contains "JK" and the SelSep is "|", so the result is "|JK|"
To make it possible to find the "JK" code at the start or end of a cell, as well as anywhere within the cell, a separator is added at the start and end of all cell text in the search range.
=SelSep&$B$2:$E$3&SelSep
The search range is $B$2:$E$3, and the SelSep is "|", so the result for the first cell in the search range is "|JK|"
The SEARCH function looks for that string in each cell of the search range. If the string is found, it return the number where that string starts in the cell. For example, in cell B2 the string is found at the start of the cell, so the result is 1. If the string is not found, the SEARCH function returns a #VALUE! error.
=SEARCH(SelSep&$C5&SelSep, SelSep&$B$2:$E$3&SelSep)
In the screen shot below, the results of the SEARCH function have been calculated by pressing the F9 key. The orange cells contain the "JK" code, so they return a number. The 3rd, 4th, and 6th cells do not contain the code, so they return an error.
Next, the SIGN function returns a 1, for each positive number. If the string was not found, it will return an error
=SIGN(SEARCH(SelSep&$C5&SelSep, SelSep&$B$2:$E$3&SelSep))
In the screen shot below, the results of the SIGN function have been calculated by pressing the F9 key. All the numbers have been changed to 1, and the 3rd, 4th, and 6th cells still show an error.
Next, the IFERROR function changes any errors to zeros.
=IFERROR(SIGN(SEARCH(SelSep&$C5&SelSep, SelSep&$B$2:$E$3&SelSep)),0)
In the screen shot below, the results of the IFERROR function have been calculated by pressing the F9 key. All the results are now 1 or zero
Finally, the SUM function calculates the total of all the numbers.
=SUM(IFERROR(SIGN(SEARCH(SelSep&$C5&SelSep, SelSep&$B$2:$E$3&SelSep)),0))
In this example, the total is 5 -- the number of cells that contain the "JK" code, anywhere in the cell, and not counting similar codes, such as "JKA"
Download the Count Specific Text Codes sample file. The zipped file is in xlsx format, and does not contain macros.
Last updated: January 26, 2023 2:09 PM