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**

- 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.
- 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?**

- 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<>””).
- 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}.

- 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.

- 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*

## Leave a Comment