ISBLANK function for multiple cells?

[copleyr]

Hello,

I am currently using the function:

=IF(OR(ISBLANK(C8), ISBLANK (D8), ISBLANK (E8), ISBLANK (F8), ISBLANK (G8), ISBLANK (H8), ISBLANK (I8), ISBLANK (J8), ISBLANK (K8), ISBLANK (L8), ISBLANK (M8), ISBLANK (N8), ISBLANK (O8), ISBLANK (P8), ISBLANK (Q8), ISBLANK (R8)), "", IF(SUM(C8:R8)=0,"Yes", "No"))

and it is returning a #value error sign.

I want it to check if C8:R8 is blank, and if so, put nothing. But if not, use the formula: IF(SUM(C8:R8)=0,"Yes", "No"))

Any suggestions?

Thanks in advance for your help.

 

[Colin Legg]

A little consideration needs to be given to the contents of the range C8:R8. If it does not contain formulas or if you want formulas that return "" to be ignored then you can use COUNTBLANK(), eg

Are any of the cells empty?
=COUNTBLANK(C8:R8)>0



However, if you want formulas that return "" to be included in the check then you can use COUNTA(), eg.

Are any of the cells empty?
=COUNTA(C8:R8)<>COLUMNS(C8:R8)





Incorporating these in your formula:
=IF(COUNTBLANK(C8:R8)>0,"",IF(SUM(C8:R8)=0,"Yes","No"))

=IF(COUNTA(C8:R8)<>COLUMNS(C8:R8),"",IF(SUM(C8:R8)=0,"Yes","No"))

 

[c_m_s_jr]

{=IF(ISBLANK(C8:R8),"",IF(SUM(C8:R8)=0,"Yes", "No"))}

confirm with control+shift+enter for the {} to be placed around the formula

 

[pgc01]

Hi Colin

Please notice that Countblank does not ignore null strings "".

 

[pgc01]

{=IF(ISBLANK(C8:R8),"",IF(SUM(C8:R8)=0,"Yes", "No"))}

Hi c_m_s_jr

I believe your formula in not correct, it's even returning an array which I think is not what you mean. You can OR the result of the Isblank

=IF(OR(ISBLANK(C8:R8)),"",IF(SUM(C8:R8)=0,"Yes", "No"))

... confirmed with cse

copleyr

This assumes that you are testing if any of the cells is empty. If they have formulas or null strings it will not work. Please confirm what you are testing.

 

[Colin Legg]

Hi Colin

Please notice that Countblank does not ignore null strings "".

Hi Pedro,

Yes it does. This might just be some crossed wires, depending on what "ignore null strings" means....

Put the formula ="" in cell A1.
Leave B1 completely empty.

Then this formula returns 2:
=COUNTBLANK(A1:B1)

The ="" formula in A1 is ignored, ie. A1 is considered to be a blank even though there is something in there, so it counts it.


Or, more to the point,
=COUNTBLANK(A1) returns 1 (ie. considers it to be blank)
whereas
=ISBLANK(A1) returns FALSE (ie. considers it not to be blank)

 

[Alee Aasim]

Use this formula

=IF(and(ISBLANK(C8:R8)),"",IF(SUM(C8:R8)=0,"Yes", "No"))

and hit Ctrl + Shift + Enter.


Hope this one will work fine.

Regards,
Aasim

 

[c_m_s_jr]

Hi c_m_s_jr

I believe your formula in not correct, it's even returning an array which I think is not what you mean. You can OR the result of the Isblank

=IF(OR(ISBLANK(C8:R8)),"",IF(SUM(C8:R8)=0,"Yes", "No"))

... confirmed with cse

copleyr

This assumes that you are testing if any of the cells is empty. If they have formulas or null strings it will not work. Please confirm what you are testing.

I appreciate the clarification pgc01!

 

[pgc01]

This might just be some crossed wires, depending on what "ignore null strings" means....

Colin

You are right, I misunderstood what you meant.

Just to make it clear

- by Empty I mean really empty, no formula, no value, the result of IsBlank is True

- by a Null String, "", I mean a string with zero characters

So when you wrote:

Are any of the cells empty?
=COUNTBLANK(C8:R8)>0

To me that was not right, as even if the cells have null strings ("") or formulas that return "", the countblanc counts them and to me they are not empty.

It's a problem of terminology, and excel doesn't help, by using the word "blank" with 2 different meanings in IsBlank and CountBlank .

Maybe the best would be to ban the term Blank.

 

[Colin Legg]

Hi Pedro,

Yes, I didn't take enough care on my first post so the phrasing was ambiguous. I knew what I meant at the time but retrospectively I wish I had written it slightly differently. I'm glad you picked it up so it doesn't confuse anyone else.

It's a problem of terminology, and excel doesn't help, by using the word "blank" with 2 different meanings in IsBlank and CountBlank

I totally agree!