Home

Yes if range contains text, no if not, and blank if range is blank

%3CLINGO-SUB%20id%3D%22lingo-sub-487978%22%20slang%3D%22en-US%22%3EYes%20if%20range%20contains%20text%2C%20no%20if%20not%2C%20and%20blank%20if%20range%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487978%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20ranges%20by%20date%20containing%20%22yes%22%20and%20%22no%22%20responses.%20I%20need%20a%20formula%20to%20look%20at%20the%20range%2C%20assign%20a%20%22Yes%22%20response%20if%20there%20are%20no%20%22no%22s%20in%20the%20range%2C%20a%20%22No%22%20response%20if%20there%20are%20any%20%22no's%22%20in%20the%20range%2C%20and%20a%20blank%20response%20if%20the%20range%20is%20blank.%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-487978%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487995%22%20slang%3D%22en-US%22%3ERe%3A%20Yes%20if%20range%20contains%20text%2C%20no%20if%20not%2C%20and%20blank%20if%20range%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328566%22%20target%3D%22_blank%22%3E%40cjohns125%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(COUNTA(%26lt%3Brange%26gt%3B)%2CIF(COUNTIF(%26lt%3Brange%26gt%3B%2C%22no%22)%2C%22no%22%2C%22yes%22)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-488119%22%20slang%3D%22en-US%22%3ERe%3A%20Yes%20if%20range%20contains%20text%2C%20no%20if%20not%2C%20and%20blank%20if%20range%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-488119%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked%20perfect%2C%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489815%22%20slang%3D%22en-US%22%3ERe%3A%20Yes%20if%20range%20contains%20text%2C%20no%20if%20not%2C%20and%20blank%20if%20range%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328566%22%20target%3D%22_blank%22%3E%40cjohns125%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
cjohns125
New Contributor

I have a spreadsheet with ranges by date containing "yes" and "no" responses. I need a formula to look at the range, assign a "Yes" response if there are no "no"s in the range, a "No" response if there are any "no's" in the range, and a blank response if the range is blank.

Any suggestions?

3 Replies

@cjohns125 , that could be

=IF(COUNTA(<range>),IF(COUNTIF(<range>,"no"),"no","yes"),"")