SUMPRODUCT with LEN
One way to solve this problem is to use theSUMPRODUCT functionwith theLEN function.
This array is then compared to cell E4, which contains the number 5.
The result is a new array containing 11 TRUE and FALSE values.
To summarize:
Each TRUE value corresponds to a cell in B5:B15 that contains 5 characters.
If a new number is entered in cell E4, the formula will recalculate and return a new result.
)wildcard.COUNTIF supports three wildcardsthat can be used in thecriteriaargument: question mark (?
), asterisk(*), or tilde (~).
A question mark (?)
matches any one character and an asterisk (*) matches zero or more characters of any kind.
In this example, we can use the question mark (?)
wildcard to count cells that contain 5 characters like this:
The “?”
five times inside COUNTIF, so the result is the same.
Note: One difference in the COUNTIF formula is that COUNTIF with “?”
as a wildcard will only count characters intext values cells that contain numeric values will not be counted.
The SUMPRODUCT + LEN formula on the other hand will count characters of any kind, including numbers.
LEN will also count characters in numbers, but number formatting is not included.
REPT Function
The Excel REPT function repeats characters a given number of times.
For example, =REPT(“x”,5) returns “xxxxx”.
