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

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Color1	Color2	Color3	Color4	Color5	Color6	Color7	Color8	Good?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Red	Orange	Yellow	Blue	Blue	Blue	Violet	Black

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[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!