0

Good afternoon,

I have a range of 3 cells A1:A3 which will contain either; "OK", "MISSING" or "Expired", or a combination of those.

I'd like a formula which will scan the range and if it contains MISSING to say missing, if it only contains OK or Expired, to show Expired. Otherwise say OK.

Every answer I've found relates to only 2 criteria with 2 answers, not 3 and 3.

I'm sure it's something to do with an IF(COUNTIFS( but I can't figure it out! Any help would be great :)

Thanks

Brian
  • 1
  • Show formula you've tried with two conditions. It should just be a matter of adding a third if condition – dbmitch Aug 04 '16 at 04:03
  • =IF(COUNTIF(A1:A3,"Expired Insurance"),"Expired Insurances","OKAY") =IF(COUNTIFS(A1:A3,"Expired Insurance", A1:A3,"MISSING"),"Missing","OKAY") I know these aren't correct.. – Brian Aug 04 '16 at 04:19
  • 1
    Perhaps something like this. =IF(A1:A3="MISSING", "MISSING", IF(A1:A3="Expired Insurance", "Expired Insurances", "OK")) – Brian Aug 04 '16 at 04:20

2 Answers2

1

You're close!

=IF(COUNTIF("MISSING",A1:A3)>0, "MISSING", IF(COUNTIF("Expired Insurance",A1:A3)>0,"Expired Insurances", "OK"))

Máté Juhász
  • 2,197
  • 1
  • 19
  • 40
  • 1
    Excellent thanks so much!! you had the criteria and range swapped around, but i got there :P – Brian Aug 04 '16 at 04:43
0

=IF(COUNTIF(A1:A3, "MISSING")>0, "MISSING", IF(COUNTIF(A1:A3, "Expired Insurance")>0,"Expired Insurances", "OK"))

Brian
  • 1