Finding ranges that don’t have blank values in MS Excel

---

The following formula will help indicate if all cells in a range are blank or not.
 
=IF(SUMPRODUCT(--(Range<>””)=0,”No”,”Yes”)

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

---