Summary

To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case. In the example shown, the formula in E5 is:

=SUMPRODUCT(--(B5:D5<>""))=0

Generic formula

=SUMPRODUCT(--(range<>""))=0

Explanation 

Working from the inside out, this formula contains an expression inside SUMPRODUCT that tests each cell in a range like so:

--(B5:D5<>"")

Inside the parentheses, the result of B5:D5<>"" looks like this:

{TRUE,FALSE,TRUE}

The double negative then converts the TRUE FALSE values to one's and zeros:

{1,0,1}

Note that the 1's in this array correspond to cells that are not blank or empty. Then, with only one array to work with, SUMPRODUCT simply multiples these values together and returns the result.

Any time the result is greater than zero, we know that not every cell in the range is blank. To force the formula to return TRUE if every cell is blank, and FALSE if not, we simply add =0 to the end of the formula.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.