Because there are three empty cells in the range C5:C16, COUNTBLANK returns 3.
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: 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: 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: 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. See also: 50 examples of formula criteria.
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: 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: 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 have a length equal to zero: You can adapt this formula to count empty cells in Group A like this: 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: The final result is 2, since there are two empty cells in Group A. Note: one reason the SUMPRODUCT syntax above is useful is because you can drop the same logical expressions into a newer function like the FILTER function to extract cells that meet the same criteria. The SUMPRODUCT function is more versatile than RACON functions like COUNTIFS, SUMIFS, etc. and you will often see it used in formulas that solve tricky problems. You can read more about SUMPRODUCT here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
