SOLVED

Contributor

# If Function help

Hi,

I am trying to create a formula which will return blank if cells a6:a8 are all NO, and to display cell b6 if this isn't correct. See attached spreadasheet. I have a formula below but it doesnt work:

=IF(AND(A6:A8="NO","",B6)))

7 Replies
best response confirmed by Robert1290 (Contributor)
Solution

# Re: If Function help

On which Excel you are?

If correct the syntax

``=IF(AND(A6:A8="NO"),"",B6)``

formula works

# Re: If Function help

Thanks Sergei, almost works. I am trying to conditionally format the formula cell to go red when the result is blank. However the result is not blank. This may be becaue a6:a8 is NO as a result of a dropdown rather than inputting "NO"

# Re: If Function help

For the conditional formatting most probably it shall be another formula, but I didn't catch the logic - you would like to highlight something if ALL in range A6:A8 are "NO"; or you'd like to highlight for each record where the value in column A is "NO".

Perhaps you may provide small sample colored manually to illustrate the idea.

# Re: If Function help

Yes I have attached what I am trying to do I have added conditional formatting in c6, and would like this to be red when one of the dropdown boxes between a6:a8 is a yes rather than a no.

# Re: If Function help

I have amended how I have done it now and managed to get what I need working. Thank you so much for your help

# Re: If Function help

That could be like

with formula

``=COUNTIF(\$A\$6:\$A\$8,"NO")<ROWS(\$A\$6:\$A\$8)``

# Re: If Function help

You are welcome. That could be done by several ways, depends on your actual data and which approach is more comfortable for you from maintenance point of view.