## Explanation

In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (**n**) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:

```
=SUMPRODUCT(N(LEN(B5:B15)>F4)) // returns 5
```

The formula returns 5 since there are five cells in B5:B15 that contain more than 40 characters.

### Reference calculation

The formula in C5, copied down, is based on the LEN function:

```
=LEN(B5) // returns 25
```

This calculation is provided for reference only and is not used by the formula above. The counts in column C make it easy to quickly check results.

### Checking length

Working from the inside out, the number of characters in each cell is calculated with the LEN function like this:

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

The LEN function runs on the range B5:B15. Because we give LEN multiple values, it returns multiple results in an array like this:

```
{25;47;46;45;42;32;36;34;36;40;46}
```

Each number in the array is the length of a cell in B5:B15. This array is evaluated with the logical expression ">F4", which creates an array of TRUE FALSE values:

```
{FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
```

Each TRUE corresponds to a cell that contains more than 40 characters, since cell F4 contains 40. To convert the TRUE and FALSE values to their numeric equivalents, we use the N function:

```
N({FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE})
```

*Note: the double negative (--) is another way to convert TRUE/FALSE to 1/0.*

The result is an array of 1s and 0s:

```
{0;1;1;1;1;0;0;0;0;0;1}
```

### Counting results

This array is returned directly to the SUMPRODUCT function, which returns the sum of numbers in the array:

```
=SUMPRODUCT({0;1;1;1;1;0;0;0;0;0;1}) // returns 5
```

The final result is 5. Since **n** is provided as a variable in cell F4, it can be changed at any time and the formula will recalculate and return a new result.

### COUNTIFS function

This is an example of a problem that can't be solved directly with the COUNTIFS function. This is because COUNTIFS requires a range and won't allow processing of an array like that returned by the LEN function above. However, if you don't mind using a helper column, you could use COUNTIFS on column C like this:

```
=COUNTIFS(C5:C15,">"&F4) // returns 5
```

The result is the same as the SUMPRODUCT formula above. Note the greater than operator (>) is enclosed in double quotes ("") and concatenated to F4.