SOLVED

If Function help

%3CLINGO-SUB%20id%3D%22lingo-sub-2209130%22%20slang%3D%22en-US%22%3EIf%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209130%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20which%20will%20return%20blank%20if%20cells%20a6%3Aa8%20are%20all%20NO%2C%20and%20to%20display%20cell%20b6%20if%20this%20isn't%20correct.%20See%20attached%20spreadasheet.%20I%20have%20a%20formula%20below%20but%20it%20doesnt%20work%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(A6%3AA8%3D%22NO%22%2C%22%22%2CB6)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2209130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209167%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981308%22%20target%3D%22_blank%22%3E%40Robert1290%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20which%20Excel%20you%20are%3F%3C%2FP%3E%0A%3CP%3EIf%20correct%20the%20syntax%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(A6%3AA8%3D%22NO%22)%2C%22%22%2CB6)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eformula%20works%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209204%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209204%22%20slang%3D%22en-US%22%3EThanks%20Sergei%2C%20almost%20works.%20I%20am%20trying%20to%20conditionally%20format%20the%20formula%20cell%20to%20go%20red%20when%20the%20result%20is%20blank.%20However%20the%20result%20is%20not%20blank.%20This%20may%20be%20becaue%20a6%3Aa8%20is%20NO%20as%20a%20result%20of%20a%20dropdown%20rather%20than%20inputting%20%22NO%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209236%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209236%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981308%22%20target%3D%22_blank%22%3E%40Robert1290%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20conditional%20formatting%20most%20probably%20it%20shall%20be%20another%20formula%2C%20but%20I%20didn't%20catch%20the%20logic%20-%20you%20would%20like%20to%20highlight%20something%20if%20ALL%20in%20range%20A6%3AA8%20are%20%22NO%22%3B%20or%20you'd%20like%20to%20highlight%20for%20each%20record%20where%20the%20value%20in%20column%20A%20is%20%22NO%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20provide%20small%20sample%20colored%20manually%20to%20illustrate%20the%20idea.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209249%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209249%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20I%20have%20attached%20what%20I%20am%20trying%20to%20do%20I%20have%20added%20conditional%20formatting%20in%20c6%2C%20and%20would%20like%20this%20to%20be%20red%20when%20one%20of%20the%20dropdown%20boxes%20between%20a6%3Aa8%20is%20a%20yes%20rather%20than%20a%20no.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209260%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209260%22%20slang%3D%22en-US%22%3EI%20have%20amended%20how%20I%20have%20done%20it%20now%20and%20managed%20to%20get%20what%20I%20need%20working.%20Thank%20you%20so%20much%20for%20your%20help%3C%2FLINGO-BODY%3E
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.