Contextures

Count Specific Text Codes

For 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.

Problem: Count Specific Text Codes

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'

formula finds 7 cells with JK

Count All Codes - COUNTIF

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:

  • Cells that start contain "JK" exactly: COUNTIF($B$2:$E$3,"=" & B15)
  • Cells that end with "|JK": COUNTIF($B$2:$E$3,"=*" & SelSep & B15)
  • Cells that start with "JK|": COUNTIF($B$2:$E$3,"=" & B15 & SelSep & "*")
  • Cells that contain "|JK|": COUNTIF($B$2:$E$3,"*" & SelSep & B15 & SelSep & "*")

Those four formulas are wrapped with a SUM function, to get the total count.

four formulas are wrapped with a SUM function

Count All Codes - SEARCH

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))

SUM, IFERROR, SIGN and SEARCH

How the SEARCH Formula Works

1. String to Look For

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|"

2. Add Separator at Start and End of All Cell Text

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|"

3. Search for that string in each cell

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.

cells return an error

4. Return a 1 if the number is positive

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.

cells still show an error

5. Replace Errors With Zeros

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

All the results are now 1 or zero

6. Calculate the Total

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"

total is 5

Download the Sample File

Download the Count Specific Text Codes sample file. The zipped file is in xlsx format, and does not contain macros.go to top

Related Links

Functions List

30 Functions in 30 Days

AGGREGATE

COUNT / COUNTIF

IFERROR

VLOOKUP

Custom Search

30 Excel Functions in 30 Days

 

 

 

 

excel tools

 

 

 

 

 

pivot power add-in

 

 

Excel chart tools

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

excel tools

 Get Excel News

 

 


 

 


 

 

30 Excel Functions in 30 Days

 

 


 

 


Last updated: December 12, 2016 1:43 PM