The following formula will help indicate if all cells in a range are blank or not.
Here is an example to further illustrate:
In the table above we want to know the name of actors who have starred in at least one of the films listed in columns B to K.
Here is how we could get this list:
The formula uses SUMPRODUCT to test if the sum of the values in each range equals to zero or not. The double hyphen (--) turns the values in the range into one’s and zeros thus allowing for the SUMPRODUCT To correctly add up numeric values. Wherever the sum of values in a range equals to zero a “Yes” will be returned, If not the value returned will be “No”.