The result is 4, since there are four cells in B5:B15 that contain five characters.
SUMPRODUCT with LEN
One way to solve this problem is to use the SUMPRODUCT function with the LEN function. In the example shown, the formula in E6 is: Working from the inside out, the LEN function is used to get the length of each value in the range like this: Since the range B5:B15 contains 11 cells, LEN returns 11 results in an array like this: Each number in the array is the length of a cell in B5:B15. 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. Next, a double-negative (–) is used to convert the TRUE and FALSE values to 1s and 0s, and the resulting array is delivered directly to the SUMPRODUCT function: SUMPRODUCT returns the sum of the array, 4, as a final result. If a new number is entered in cell E4, the formula will recalculate and return a new result.
COUNTIF function
Another way to solve this problem is with the COUNTIF function and the question mark (?) wildcard. COUNTIF supports three wildcards that can be used in the criteria argument: 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 “?” symbol is a wildcard in Excel that means “match any single character”, so this pattern will count cells that contain five characters. To adapt the formula above to use n from cell E4, we can add the REPT function like this: The REPT function repeats 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 in text 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
