If anything but S
,I
, or R
is in those two cells this will return an empty string:
=IF(AND(OR(G4={"S","I","R"}),OR(I4={"S","I","R"})),...,"")
Where ...
is your formula.
If all you care about is whether blank or not in those two cells it can be:
=IF(AND(G4<>"",F4<>""),...,"")
We can also use SUMPRODUCT(COUNTIFS())
to lessen the use of OR
s and AND
s:
IF(SUMPRODUCT(COUNTIFS(G4,{"S","I","R"},I4,{"S","I","R"})),"PASS","FAIL")
Though this approach does not shorten the current formula greatly, it would if the criteria were to increase to more than 3.
So:
=IF(AND(OR(G4={"S","I","R"}),OR(I4={"S","I","R"})),IF(SUMPRODUCT(COUNTIFS(G4,{"S","I","R"},I4,{"S","I","R"})),"PASS","FAIL"),"")
"Fail"
to""
or am I missing something?