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

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

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?  Reply With Quote

2. 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.  Reply With Quote

3. let me try that. ill reply shortly.  Reply With Quote

4. 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  Reply With Quote

5. 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.  Reply With Quote

6. 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?  Reply With Quote

7. 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?  Reply With Quote

8. 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)))  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•