# How to tell formula to ignore blank cells?

#### leemim

##### New Member
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?

Many thanks in advance!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### AlanY

##### Well-known Member
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

Last edited:

#### leemim

##### New Member
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

##### Well-known Member
Re: URGENT - How to tell formula to ignore blank cells?

yes, you're right.

Last edited:

#### AlanY

##### Well-known Member
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

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

the final formula will looks like

Code:
``=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")``

Last edited:

Replies
15
Views
363
Replies
1
Views
261
Replies
13
Views
1K
Replies
1
Views
163
Replies
15
Views
2K

1,195,712
Messages
6,011,248
Members
441,596
Latest member
reza_57

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back