All cells in range are blank

  • Post author:
  • Post category:Uncategorized

We can use the SUMPRODUCT function in Excel to check or test if all cells in a range are blank. If all cells in the specified range are empty, the function will return TRUE. But if not all cells are empty, then it will return a FALSE. This article provides a step-by-step guide on how to test if all cells are empty using the SUMPRODUCT function based on some logical tests.

Figure 1: Final result

Syntax of the formula

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

Explanation of the use case

The SUMPRODUCT function returns a TRUE if cells in range are blank or empty, and FALSE if not all cells are empty. In the example above, the formula in cell E2 is as below:

=SUMPRODUCT(--(A2:D2<>””))=0

The inner part of the SUMPRODUCT formula, i.e. ‘- -(A2:D2<>””) tests each cell in the given range.

Inside the parentheses, we have A2:D2<>””, which looks like: {TRUE, FALSE, TRUE}

Outside the parentheses we have the double hyphen, which is responsible for converting the TRUE FALSE into 1s and 0s, i.e. {1,0,1}

Remember that 1s in this case represents cells that are not empty. The SUMPRODUCT function will then multiply the values and return the expected value, either TRUE or FALSE.

Usually, the result of the multiplication will be greater than zero. But we know that not every cell in the range will be empty. So we shall have to add =0 at the end of the formula in order to force it return TRUE if the cells are empty, and FALSE if not empty.

Example

 Figure 2: Finding empty cells in a class list

The table above shows a list of students’ names and their marks. We want to find which cells are empty in the range.

-In column C, we specify the result. In cell C2, we specify our formula,

=SUMPRODUCT(- - (A2:B2<>””))=0

-We then press Enter to get the result in cell C2.

-We then copy down the formula to get the results for the other cells.