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.
Explanation
In this example, the goal is to count the number of cells in a range that contain negative numbers. For convenience, the range B5:B15 is named data . This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below.
COUNTIF function
The COUNT function counts the number of cells in a range that match the supplied criteria. For example, you can use COUNTIF like this:
=COUNTIF(range,"red") // count cells equal to "red"
=COUNTIF(range,100) // count cells equal to 100
=COUNTIF(range,">10") // count cells greater than 10
To count negative numbers in this example, we need to use the less than operator (<) with zero like this:
=COUNTIF(data,"<0") // returns 3
To include zero in the count, use the less than or equal to operator (>=):
=COUNTIF(data,"<=0") // returns 4
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and Boolean algebra :
=SUMPRODUCT(--(data<0))
Working from the inside out, this expression checks if values in data (B5:B15) are less than zero:
data<0
Because data contains eleven cells, the result from this expression is an array that contains 11 TRUE and FALSE values:
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (–):
--{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
The resulting array inside the SUMPRODUCT function looks like this:
=SUMPRODUCT({0;0;0;1;0;0;1;0;1;0;0}) // returns 3
With a single array to process, SUMPRODUCT sums the array and returns 3 as the result.
