Checking a Range in an If statement

[mlopes1]

I have the following formula in cell A1 to check if cells C126:130 are all blank:


=IF((ISBLANK(C126)*AND(ISBLANK(C127)*AND(ISBLANK(C128)*AND(ISBLANK(C129)*AND(ISBLANK(C130)))))),"X","Y")

The formula works correctly but is there a way to reference all 5 cells, i.e. (ISBLANK(C126:130)) rather than nesting the AND statements? I tried using the (C126:130) in the AND argument but then it was only working correctly when C126 was not blank.

Thanks in advance,

Mark

 

[lenze]

If the cells are truely Blank, why not use

=IF(COUNTA(C126:C130)=0,"X","Y")
This message was edited by lenze on 2002-08-28 08:39

 

[Aladin Akyurek]

On 2002-08-28 08:30, mlopes1 wrote:
I have the following formula in cell A1 to check if cells C126:130 are all blank:


=IF((ISBLANK(C126)*AND(ISBLANK(C127)*AND(ISBLANK(C128)*AND(ISBLANK(C129)*AND(ISBLANK(C130)))))),"X","Y")

The formula works correctly but is there a way to reference all 5 cells, i.e. (ISBLANK(C126:130)) rather than nesting the AND statements? I tried using the (C126:130) in the AND argument but then it was only working correctly when C126 was not blank.

Thanks in advance,

Mark

=IF(COUNTBLANK(C126:130),"X","Y")

will give you an X even when C126:130 houses at least a blank.

 

[mlopes1]

I did not even consider using the CountA feature. That works perfectly. Thank you again, as usual, very helpful!

Mark