Using IF and ISBLANK statement to determine a range of cells if any of them have information in them

Solution 1:

COUNTA will give the number of cells in a range that are not empty.

So try ...

 =IF(NOT(COUNTA(BM2:BQ2)),"","Major Milestone Due")

Solution 2:

Your formula is correct if it is entered as an array formula.

The isblank doesn't work for an array unless you enter it as an array formula by using ctrl + shift + enter. It will look like this by having curly brackets around it:

{=IF(ISBLANK(BM2:BQ2),"","Major Milestone Due")}

Also, an if - counta will do this no problem as well -

=IF(COUNTA(BM2:BQ2)=0,"","Major Milestone Due")

Solution 3:

An important thing to consider is your definition of "blank". Based on the fact that you used the wording "I want to return another blank", it would appear that you're defining "blank" as cells that appear blank, but which may actually contain a formula that outputs "" and therefore aren't empty.

The COUNTA and ISBLANK methods won't work if you want to handle these sorts of cells as blanks, since those two formulas look for truly empty cells. To handle formulas which output "", you have two options:

• If you always know the size of your range, you can use either of the following:
  
  =IF( COUNTBLANK(BM2:BQ2)=5, "", "Major Milestone Due")
=IF( COUNTIF(BM2:BQ2,"")=5, "", "Major Milestone Due")
  
  where 5 is the size of your range. This method does not work as well with dynamic ranges.

• A slightly more complex formula (at least, more complex to explain!) uses SUMPRODUCT:
  
  =IF( SUMPRODUCT(--(BM2:BQ2<>""))=0, "", "Major Milestone Due")

(Note that COUNTIF(BM2:BQ2,"<>") has the same issue as COUNTA.)