0

I am using the following statement and its working but i would like the cell to be blank until S, I or R are entered into G4 and I4. At the moment it states Fail.

=IF(OR(AND(G4="S", I4="S"), AND(G4="R", I4="R"), AND(G4="I",I4="I")), "Pass", "Fail")

Any suggestions?

1
  • Just change "Fail" to "" or am I missing something?
    – Alex M
    Oct 17, 2019 at 17:21

1 Answer 1

0

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 ORs and ANDs:

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"),"")
2
  • Thanks for your help, the last one using SUMPRODUCT does exactly what i was looking for.
    – Abi
    Oct 17, 2019 at 15:55
  • If this worked for you then please consider marking as correct by clicking the check mark by the answer. Oct 17, 2019 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .