How to tell formula to ignore blank cells?

[leemim]

Hi all,

I'm using the following formula to tell me whether or not services are provided at certain locations or not. The issue that I am having is that whenever any of the cells are blank, it automatically populates as "Yes" (because the values are not being met since the cell is blank). Can someone please tell me how I can amend this formula, so that it ignores any blank cells?

=IF(AND(OR(T2="No",U2="Other"),OR(AD2="No",AE2="Other"),OR(AZ2="NO",BA2="Other"),OR(BJ2="NO",BK2="Other"),OR(BT2="NO",BU2="Other"),OR(CD2="NO",CE2="Other"),OR(CN2="NO",CO2="Other"),OR(CX2="NO",CY2="Other"),OR(DH2="NO",DI2="Other"),OR(DR2="NO",DS2="Other"),OR(EA2="NO",EB2="Other"),OR(EJ2="NO",EK2="Other"),OR(ES2="NO",ET2="Other"),OR(FB2="NO",FC2="Other")),"No","Yes")

Many thanks in advance!

 

[AlanY]

Re: URGENT - How to tell formula to ignore blank cells?

you can amend your OR() statementts from OR(T2="No",U2="Other") to OR(T2="No",U2="Other",T2= "",U2= "") etc

what's a typical Row 2 looks like? there may be other options

 

[leemim]

Re: URGENT - How to tell formula to ignore blank cells?

Hi Alan,

Won't doing that mean that if any of the cells are blank, it will automatically put it down as 'No'?

Row 2 is just the first property in question, but there are over 4000 properties.
Columns T+U, AD+AE, AZ+BA etc. tell me whether we supply a service + and then who provides that service.

 

[AlanY]

Re: URGENT - How to tell formula to ignore blank cells?

yes, you're right.

 

[AlanY]

Re: URGENT - How to tell formula to ignore blank cells?

give this a try

amend the 14 pairs of T+U, AD+AE etc to

IF(OR(T2="No",U2="Other",AND(T2="",U2="")),1,0), and
IF(OR(AD2="No",AE2="Other",AND(AD2="",AE2="")),1,0) etc

add them alltogether, if they equal to 14 then NO, otherwise YES.

the final formula will looks like

=IF(IF(OR(T2="No",U2="Other",AND(T2="",U2="")),1,0)+IF(OR(AD2="No",AE2="Other",AND(AD2="",AE2="")),1,0)+.........=14,"NO","YES")