# if a cell in a range is blank, it should return an error.Do i use a formula for that?

#### chaos247

##### New member
Hi,

I have a range of cells that have a drop down list in them. These affect an ovearall percentage. I need to ensure that every cell in that range has a Yes/No/N\A. If even just one cell does not have a value, then it must return an error.

Is there a formula for that?

#### NBVC

##### Super Moderator
Staff member
You can use possibly COUNTBLANK

e.g.

=IF(COUNTBLANK(X1:X10)>0,"Error",your-formula)

where X1:X10 is your range of cells, and all either have a value or not.

#### chaos247

##### New member
let me try that. ill reply shortly.

#### chaos247

##### New member
The formula im using is =IF(OR(E20="",E21="",E22),"",SUMIF(E20:E22,"Yes",\$C20:\$C22)/(1-SUMIF(E20:E22,"N/A",\$C20:\$C22)/SUM(\$C20:\$C22))*IF(COUNTIFS(\$D20:\$D22,"auto zero",E20:E22,"No"),0,1))

When applying your part i end up with:

=IF(COUNTBLANK(X1:X10)>0,"Error",IF(OR(E20="",E21="",E22),"",SUMIF(E20:E22,"Yes",\$C20:\$C22)/(1-SUMIF(E20:E22,"N/A",\$C20:\$C22)/SUM(\$C20:\$C22))*IF(COUNTIFS(\$D20:\$D22,"auto zero",E20:E22,"No"),0,1))

The part highlighted in red is designed to keep the cell blank, however when i apply your part of the formula, the cell value is error until all relevant cells are populated. Then the percentage is displayed. See E23. File attached

#### Attachments

• Test sheet 3_etaf 2.xlsm
45.8 KB · Views: 8

#### NBVC

##### Super Moderator
Staff member
you can pull it to the front:

=IF(OR(E20="",E21="",E22),"",IF(COUNTBLANK(X1:X10)>0,"Error",SUMIF(E20:E22,"Yes",\$C20:\$C22)/(1-SUMIF(E20:E22,"N/A",\$C20:\$C22)/SUM(\$C20:\$C22))*IF(COUNTIFS(\$D20:\$D22,"auto zero",E20:E22,"No"),0,1)))

you may forgotten to include the ="" after E22 in that red part.... check again.

#### chaos247

##### New member
IF i copy your formula in E23

=IF(OR(E20="",E21="",E22=""),"",IF(COUNTBLANK(X1:X10)>0,"Error",SUMIF(E20:E22,"Yes",\$C20:\$C22)/(1-SUMIF(E20:E22,"N/A",\$C20:\$C22)/SUM(\$C20:\$C22))*IF(COUNTIFS(\$D20:\$D22,"auto zero",E20:E22,"No"),0,1)))

Even if all the drop downs are Yes or No, the value in E23 is still error? Am i doing something wrong?

#### NBVC

##### Super Moderator
Staff member
I am not sure I understand then.

This part: IF(OR(E20="",E21="",E22=""),""... says to that if any of the 3 cells are blank, return a blank... and this part: IF(COUNTBLANK(E20:E21)>0,"ERROR".. (i used X1:X10) in my example, for you to adjust to your data) would say if any of the cells are blank return "ERROR".

I am not sure which you want?

#### JeffreyWeir

##### Super Moderator
Staff member
Looking at your spreadsheet, I think you want something like this:
=IF(COUNTBLANK(E20:E22)=3,"",IF(COUNTBLANK(E20:E22)<3,"Not Finished",SUMIF(E20:E22,"Yes",\$C20:\$C22)/(1-SUMIF(E20:E22,"N/A",\$C20:\$C22)/SUM(\$C20:\$C22))*IF(COUNTIFS(\$D20:\$D22,"auto zero",E20:E22,"No"),0,1)))

Note I made it say "Not Finished" rather than "Error".

Edit: Whoops, I meant to put this:
=IF(COUNTBLANK(E20:E22)=3,"",IF(COUNTBLANK(E20:E22)>0,"Not Finished",SUMIF(E20:E22,"Yes",\$C20:\$C22)/(1-SUMIF(E20:E22,"N/A",\$C20:\$C22)/SUM(\$C20:\$C22))*IF(COUNTIFS(\$D20:\$D22,"auto zero",E20:E22,"No"),0,1)))

Last edited: