SOLVED

IF formula on various texts

%3CLINGO-SUB%20id%3D%22lingo-sub-1430728%22%20slang%3D%22en-US%22%3EIF%20formula%20on%20various%20texts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430728%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20at%20a%20formula%20to%20show%20IF%20any%20of%204%20cells%20shows%20%22YES%22%20then%20the%20auto%20input%20should%20show%20YES%2C%20otherwise%20it%20should%20show%20NO.%20How%20do%20i%20do%20this%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1430728%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430878%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20on%20various%20texts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F685385%22%20target%3D%22_blank%22%3E%40Ian_Grant%3C%2FA%3E%26nbsp%3B%20simply%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(OR(cell1%3D%22Yes%22%2C%20cell2%3D%22Yes%22....)%2C%20%22Yes%22%2C%20%22No%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20you%20could%20make%20it%20more%20robust%20doing%20things%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(OR(UPPER(LEFT(cell1%2C1))%3D%22Y%22%2C%20UPPER(LEFT(cell2%2C1))%3D%22Y%22%2C....)%2C%20%22Yes%22%2C%20%22No%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eso%20that%20%22yes%22%2C%20%22Yes%22%2C%20%22YES%22%2C%20%22Y%22%2C%20or%20%22y%22%20are%20all%20counted%20as%20%22Yes%22%20(but%20so%20will%20any%20other%20entry%20starting%20with%20%22y%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442851%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20on%20various%20texts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442851%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F685385%22%20target%3D%22_blank%22%3E%40Ian_Grant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(SUMPRODUCT(--(LEFT(A1%3AD1)%3D%22y%22))%2C%22Yes%22%2C%22No%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am looking at a formula to show IF any of 4 cells shows "YES" then the auto input should show YES, otherwise it should show NO. How do i do this please.

2 Replies
Highlighted
Best Response confirmed by Ian_Grant (New Contributor)
Solution

@Ian_Grant  simply:

=IF(OR(cell1="Yes", cell2="Yes"....), "Yes", "No")

but you could make it more robust doing things like:

=IF(OR(UPPER(LEFT(cell1,1))="Y", UPPER(LEFT(cell2,1))="Y",....), "Yes", "No")

so that "yes", "Yes", "YES", "Y", or "y" are all counted as "Yes" (but so will any other entry starting with "y")

Highlighted

@Ian_Grant 

As variant

=IF(SUMPRODUCT(--(LEFT(A1:D1)="y")),"Yes","No")