SOLVED

If Function help

Contributor

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

@Robert1290 

On which Excel you are?

If correct the syntax

 

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

 

formula works

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"

@Robert1290 

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.

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.

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

@Robert1290 

That could be like

image.png

with formula

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

@Robert1290 

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.