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.
Explanation
In this example, the goal is to count cells in a range that are blank. Counting blank cells in Excel can be tricky because cells can look blank even when they are not actually empty. The article below explains three different approaches.
COUNTBLANK function
The simplest way to count empty cells in a range is to use the COUNTBLANK function . In the example shown, the formula in F6 is:
=COUNTBLANK(C5:C16) // returns 3
Because there are three empty cells in the range C5:C16 , COUNTBLANK returns 3. COUNTBLANK is fully automatic, so there is nothing to configure.
COUNTIFS function
You can also use the COUNTIFS function to count empty cells by passing in an empty string ("") as criteria like this:
=COUNTIFS(C5:C16,"") // returns 3
COUNTIF returns the same result as COUNTBLANK: 3.
Because COUNTIFS can handle multiple criteria , you can easily extend this formula to count empty cells in Group “A” like this:
=COUNTIFS(B5:B16,"A",C5:C16,"") // returns 2
The first range/criteria pair selects cells that are in Group A only. The second range/criteria pair selects empty cells. The result from COUNTIFS is 2, since there are two empty cells in Group A. You can swap the order of the range/criteria pairs with the same result.
SUMPRODUCT function
Another way to count blank cells is with the SUMPRODUCT function. You can use the SUMPRODUCT function to count empty cells like this:
=SUMPRODUCT(--(C5:C16=""))
The expression C5:C16="" returns an array that contains 12 TRUE and FALSE values, and the double negative (–) converts the TRUE and FALSE values to 1s and 0s:
{0;0;1;0;0;1;0;0;0;1;0;0} // returns 3
The result is 3 as before.
You can extend the logic used in SUMPRODUCT with other functions as needed. For example, the variant below uses the LEN function to count cells that have a length equal to zero:
=SUMPRODUCT(--(LEN(C5:C16)=0)) // returns 3
You can adapt this formula to count empty cells in Group A like this:
=SUMPRODUCT((LEN(C5:C16)=0)*(B5:B16="A"))
This is an example of using Boolean algebra in a formula. The double negative (–) is no longer needed in this case because the math operation of multiplying the two arrays together automatically converts the TRUE and FALSE values to 1s and 0s:
=SUMPRODUCT({0;0;1;0;0;1;0;0;0;0;0;0}) // returns 2
The final result is 2, since there are two empty cells in Group A.
