Combining IFS formulas

Question

Hi, I'm trying to make a formula to say "yes" if certain conditions are right for two columns but I'm unsure how to make the formula. I usually use IF, but the G column cell is acceptable if the result is >=0.5 OR 1+,2+,3+, or 4+ based on if it went on an instrument or was manually read. The H column cell is acceptable if the result is <0.080 or "clear". I need the I column cell to say yes if both the G and H column cells are acceptable. I also don't want anything to show up in the I column cell if the other two cells are blank. I tried using IFS but couldn't get it to work right because I had separate IFS for each column and couldn't figure out how to combine them. I've attached an example of the worksheet I'm using. Ignore that the cells say 24, I was playing further down in the worksheet. Any help is appreciated. Thanks!

varsdigital · Answer

You can do it in one IF: test each column with OR, then join the two with AND. In I24:=IF(AND(G24="",H24=""),"",IF(AND(OR(AND(ISNUMBER(G24),G24&gt;=0.5),G24="1+",G24="2+",G24="3+",G24="4+"),OR(AND(ISNUMBER(H24),H24&lt;0.08),H24="Clear")),"Yes","No"))How it reads: if G24 and H24 are both empty, show nothing. Otherwise G passes if it is a number of 0.5 or more, or one of 1+, 2+, 3+, 4+. H passes if it is a number under 0.08, or Clear. It shows Yes only when both pass, and No otherwise.The ISNUMBER parts keep the &gt;=0.5 and &lt;0.08 checks for numbers only, so a typed result like 1+ or Clear is judged only by its own text check.If you'd rather leave I24 empty until both G and H are filled in, change the first AND to OR. And if you want a blank instead of No, replace "No" with "".(Drafted with AI assistance; formulas tested before posting.)

Forum Discussion

Combining IFS formulas