## Explanation

In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters **n** is provided as a variable in cell E4.

### 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:

```
=SUMPRODUCT(--(LEN(B5:B15)=E4))
```

Working from the inside out, the LEN function is used to get the length of each value in the range like this:

```
LEN(B5:B15)
```

Since the range B5:B15 contains 11 cells, LEN returns 11 results in an array like this:

```
{5;4;5;6;5;4;6;6;5;4;7}
```

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:

```
=LEN(B5:B15)=E4
={5;4;5;6;5;4;6;6;5;4;7}=5
={TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
```

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({1;0;1;0;1;0;0;0;1;0;0}) // returns 4
```

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:

```
=COUNTIF(B5:B15,"?????") // returns 4
```

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:

```
=COUNTIF(B5:B15,REPT("?",E4))
```

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