Explanation
In this example, the goal is to count numbers that appear in column B. The COUNT function is designed to only count numeric values, but because all values in the range B5:B15 are text , COUNT will return zero. One approach is to split the characters in each text value into an array , then use the COUNT function to count numbers in the result. This approach is described below.
Create the array
The first step is to split the text string into an array of characters. This is done with MID, LEN, and SEQUENCE like this:
MID(B5,SEQUENCE(LEN(B5)),1)
In a nutshell, the LEN function returns the length of the text in B5, which is 9, to the SEQUENCE function as the rows argument. SEQUENCE then generates an a numeric array like {1;2;3;4;5;6;7;8;9}, which is returned to the MID function as the start_num argument:
=MID(B5,{1;2;3;4;5;6;7;8;9},1)
The MID function then extracts each of the 9 characters and returns an array like {“1”;“8”;" “;“a”;“p”;“p”;“l”;“e”;“s”}. Read a more detailed explanation here .
Count numbers
Back in the original formula, we use a double-negative (–) operation to force Excel to try and convert each character to a number:
=COUNT(--{"1";"8";" ";"a";"p";"p";"l";"e";"s"})
Where this operation succeeds, we get a numeric value. Where it fails, we get a #VALUE! error:
=COUNT({1;8;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})
The COUNT function then counts the numbers in the array and returns a final result of 2. As the formula is copied down column D, it returns a count of the numbers in each text string in column B.
Legacy Excel
In Legacy Excel , which lacks dynamic arrays and the SEQUENCE function, it is more challenging to split a text string to an array of characters. One workaround is to use the ROW and INDIRECT function like this:
=COUNT(--MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))
The INDIRECT function is a way of creating a valid Excel reference with text. The ROW function then evaluates the text and returns a reference. The reference in this case is 1:9 (rows 1 through 9), and the ROW function then returns an array of corresponding row numbers. The resulting array is the same as with the SEQUENCE function above:
=COUNT(--MID(B5,{1;2;3;4;5;6;7;8;9},1))
In the end, this formula returns the same result as above, 2. Note this is an array formula and must be entered with control + shift + enter in older versions of Excel.
Explanation
For each cell in the range, SUBSTITUTE removes all the o’s from the text, then LEN calculates the length of the text without o’s. This number is then subtracted from the length of the text with o’s.
Because we are using SUMPRODUCT, the result of all this calculation is a list of items (an array), where there is one item per cell in the range, and each item a number based on the calculation described above. In other words, we have a list of character counts, with one character count per cell.
SUMPRODUCT then sums the numbers in this list and returns a total for all cells in the range.
SUBSTITUTE is a case-sensitive function, so it will match case when performing a substitution. If you need to count both upper and lower case occurrences of a specific character, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before the substitution occurs. Then supply an uppercase character for the text that’s being counted.
The modified generic form of the formula looks like this:
=SUMPRODUCT(LEN(rng)-LEN(SUBSTITUTE(UPPER(rng),TXT,"")))
