All cells in range are blank

This article provides you with how to check or test if the cells in an array or range of cells is empty or blank. This can be done using the SUMPRODUCT function whose generic formula is as given below.

Generic formula

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

Note on usage

  1. This function returns a TRUE when a cell or the range of cells are empty. Then, this is subjected to a logical test that fits or suits the use case.
  2. Excel functions ISBLANK and COUNT A functions can also be used to look for empty cells, but there is a problem associated with them since these functions look for truly empty cells and you are left with options either using an IF and COUNTBLANK (this does not work with the dynamic range of cells) and the other by using IF and SUMPRODUCT (which is more complex).

How does the formula work?

  1. The formula contains the SUMPRODUCT function that tests each cell in an array of cells with the following part of the formula inside parenthesis i.e, ‘–(range<>””).
  2. The ‘double hyphen’ in the formula is used to convert the TRUE and FALSE  values to ones and zeros where a 1 is returned for TRUE and 0 for a FALSE.

           Ex:-{TRUE:FALSE;FALSE} gets converted to {1;0;0}.

  1. The logic value ‘1’ here represents the cells that are not blank. Then, left out with only one  

          array to work on, the SUMPRODUCT multiplies these value and returns the result.            

  1.  Now at anytime the result can be greater than zero and we know that not every cell can

          be blank in an array of cells. Therefore we add a 0 at the end to force down the function           

          to return a FALSE if even one cell was  FALSE, not blank and a TRUE if all the cells

          were blank cells.

Example

The Example given below shows that the SUMPRODUCT used for checking blank cells that returns a logical FALSE, even if any one of the cells contain a numeric value and returns a logical TRUE if the range of cells is empty.

Figure 1. Shows the SUMPRODUCT function used to check whether all cells in a range are blank

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar