formula help: conditional format of one column based on "YES"/"NO" content of four others!

Scenario

Attempting to color code column S to match a pie chart on a separate tab named ‘Metrics’. Pie chart successful based on data below in columns J, L, N, and Q using stacked conditional formatting, one example:

=COUNTIFS('CLOSED OUT'!J2:J164,"NO",'CLOSED OUT'!L2:L164,"NO", 'CLOSED OUT'!N2:N164,"NO",'CLOSED OUT'!Q2:Q164,"NO")




For all rows 2-150 (row 1 is header w/ filter):

Format cell color in column “S” based on multiple values in columns J, L, N, and Q

IF J = “NO”, AND L = “NO”, AND N=”NO”, AND Q=”NO”, S = light blue fill
IF J = “YES”, AND L = “NO”, AND N=”NO”, AND Q=”NO”, S = light red fill
IF J = “YES”, AND L = “YES”, AND N=”NO”, AND Q=”NO”, S = light green fill
IF J = “YES”, AND L = “NO”, AND N=”YES”, AND Q=”NO”, S = light purple fill



I have tried: (below all have errors)
=AND(J2="NO";L2="NO";N2="NO";Q2="NO")

=AND($J$2="NO";$L$2="NO";$N$2="NO";$Q$2="NO")

=AND($J:$J="NO";$L$L="NO";$N$N="NO";$Q$Q="NO")

=IF(AND($J2="NO",$L2="NO",$N2="NO",Q2="NO"))

=IF($J$2="NO"),IF($L$2="NO"),IF($N$2="NO"),IF($Q$2="NO")

The closest I have come are the following, each do not flag as “error” but shade incorrect cells – rows 1 (header) and 3, but not row 2 or any other.
=AND(J="NO";L="NO";N="NO";Q="NO")
=AND($J2="NO",$L2="NO",$N2="NO",Q2="NO")


The theoretical purpose is to track supplier items:
Column J, decision to proceed with sample evaluation, yes or no
Column L, sample evaluated by group test, yes or no
Column N, sample evaluated by individual tester, yes or no
Column Q, sample was rejected (for this formula, I am only interested in column Q = “NO” but there could be the need to expand this in the future)

The formula will show:
blue, = reject sample without evaluation
(J = NO, L = NO, N=NO, Q=NO)
red, = reject sample after initial evaluation
(J = YES, L = NO, N=NO, Q=NO)
green, = reject sample after initial eval AND group eval (color will be changed to light orange)
(J = YES, L = YES, N=NO, Q=NO)
purple, = reject sample after initial eval AND solo eval
(J = YES, L = NO, N=YES, Q=NO)

Future state:
(COLOR) = approve sample after initial eval and group eval
(J = YES, L = YES, N = NO, Q = YES)
(COLOR) = approve sample after initial eval and solo eval
(J = YES, L = NO, N = YES, Q = YES)

For the two desired conditional formatting shown on the sample sheet, you can use the formulas like below.....

For light green

Please Login or Register  to view this content.

For light purple

Please Login or Register  to view this content.

Same way you can set conditional formatting for other rules as well.

Columnar values:
Value of any YES in column J = 1
Column L = 2
column N = 4
column Q = 8

In column R, or elsewhere, calculate the sum of the YESes:

Please Login or Register  to view this content.

Where:

{1,0,2,0,4,0,0,8}

Represents the values of columns J:Q
* Note that the value of the sums ranges over 0 - 15, even though your sample only requires four, namely: 0, 1, 3 & 5

Conditional format column S:

Please Login or Register  to view this content.

, etc.

Let me explain you that what wrong you did with your formulas used for conditional formatting.
You applied conditional formatting rule for entire column S i.e. $S:$S. And for each cell in col. S, the formula should look for values in corresponding cells in cols. J. L. N and Q.
When you applied a conditional formatting for a range, the formula should always refer to the first cell of that range where you want to set conditional formatting.
In col. $S:$S, the first cell is S1 and corresponding dependent cells are J1, L1, N1 and Q1 so your formula should be like this.....

Please Login or Register  to view this content.

This formula is applied to cell S1 (being the first cell in the range) and checks the row 1 in the referenced cols and and if meets the criteria the S1 will be filled with desired color.
Now what you did, you used the following formula for conditional formatting.....

Please Login or Register  to view this content.

and since this formula is applied to the range $S:$S, it will first check for cell S1 and look into row 2 of the referenced cols. J, L, N and Q i.e. J2, L2, N2 and Q2. So if J2, L2, N2 and Q2 are true as per the conditions, the conditional formula will color the cell S1 (being the first cell in the range for which this conditional formula is applied), which you would not want certainly.

Glad I could help. Thanks for the feedback.