For reasons explained below, we do this with the LEN function and the SUM function.
This formula is dynamic and will recalculate immediately if source data is changed.
The approach isexplained in more detail here.
For more details, see this training video:How to filter with multiple criteria.
COUNTA
It is possible to write a simpler formula that relies on theCOUNTA function.
However, an important caveat is that COUNTA will return 1 when there are no matching values.
It will also include empty cells that meet criteria.
The formula based on LEN and SUM is a better option.
For a more general discussion of dynamic array alternatives, seeAlternatives to Dynamic Array Formulas.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
LEN will also count characters in numbers, but number formatting is not included.
SUM Function
The Excel SUM function returns the sum of values supplied.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
COUNTA does not count empty cells.
Related videos
How to filter with multiple criteria
