This article shows how to count values by length based on their character count. This method can be helpful to check your data against invalid entries. It is also a good practice to use SUMPRODUCT and N functions together.
Syntax
=SUMPRODUCT(N(LEN(range)>character limit))
Steps
- Start with =SUMPRODUCT( to handle array
- Continue with N( to return numeric values
- LEN with the range for lengths LEN(B3:B10)
- Type the condition that needs to be checked >F3
- Type )) and press Enter to complete the formula
How
Once again, the SUMPRODUCT function saves the day when we need to count values by length. The function's ability to evaluate arrays allow us to create formulas without messing with array formulas. The LEN function returns the character count of a cell. If it is used with a range, it returns an array of character counts.
LEN(B3:B10) returns {5;6;10;7;6;5;8;6}
Note: For more information and samples about the LEN function, please see How to count the number of characters inside a cell.
A logical statement with an array, will return another array. It will consist of TRUE/FALSE values.
LEN(B3:B10)>F3 returns {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}
To make the SUMPRODUCT return the count of logical statement, we need numbers instead of Boolean (TRUE/FALSE) values. The N function can help to convert the TRUE/FALSE values into 1/0 values that can be summed by SUMPRODUCT.
N(LEN(B3:B10)>F3) returns {0;0;1;1;0;0;1;0}
Now that we have 1's for cells meeting our condition and 0's for the rest; summing them gives the count of values that meets the condition.
=SUMPRODUCT(N(LEN(B3:B10)>F3)) returns 3
