Checking a range for specific criteria

[Reboshua]

I'd like to put a formula in I2 that checks a2:h2 to see if this range of colors conforms to what I need.

The range A2:H2 must contain at least:

1 Red
1 Orange
1 Yellow
3 Blue
1 Violet
1 [Any of the above colors]

If the range conforms to the restrictions, I'd like it to leave a blank cell. If it does NOT conform, I'd like it to print "NO!"

I am hoping there is a better way to do this than a big nested IF statement.

Thanks!

Excel 2016 (Windows) 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]Color1[/TD]
[TD]Color2[/TD]
[TD]Color3[/TD]
[TD]Color4[/TD]
[TD]Color5[/TD]
[TD]Color6[/TD]
[TD]Color7[/TD]
[TD]Color8[/TD]
[TD]Good?[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]Red[/TD]
[TD]Orange[/TD]
[TD]Yellow[/TD]
[TD]Blue[/TD]
[TD]Blue[/TD]
[TD]Blue[/TD]
[TD]Violet[/TD]
[TD]Black[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

 

[shknbk2]

There is nesting, but it's not nested IFs. I think this works okay for the formula in I1:

=IF(AND(OR(COUNTIF(A1:H1,"=Red")=2,COUNTIF(A1:H1,"=Red")=1),OR(COUNTIF(A1:H1,"=Orange")=2,COUNTIF(A1:H1,"=Orange")=1),OR(COUNTIF(A1:H1,"=Yellow")=2,COUNTIF(A1:H1,"=Yellow")=1),OR(COUNTIF(A1:H1,"=Blue")=4,COUNTIF(A1:H1,"=Blue")=3),OR(COUNTIF(A1:H1,"=Violet")=2,COUNTIF(A1:H1,"=Violet")=1),SUM(COUNTIF(A1:H1,"=Red"),COUNTIF(A1:H1,"=Orange"),COUNTIF(A1:H1,"=Yellow"),COUNTIF(A1:H1,"=Blue"),COUNTIF(A1:H1,"=Violet"))=8),"","NO!")

 

[Reboshua]

shkbbk2 - thank you, this worked well!