Checking a range for specific criteria

Reboshua

Board Regular
Joined
Jan 23, 2015
Messages
83
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]​
Color1Color2Color3Color4Color5Color6Color7Color8Good?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
RedOrangeYellowBlueBlueBlueVioletBlack

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
There is nesting, but it's not nested IFs. I think this works okay for the formula in I1:

Code:
=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!")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top