Conditional format on basis of other cells color

[excel_1317]

I have students data with their %marks in each Subject. I have applied conditional formatting on the basis os marks percentage. eg: Green if Marks is above 80 %, Yellow is marks in between 40% to 60%, Red if marks ia below 40%.

Now in first column, there is students names. I want if i have total of 6 subjects, student having 5 or 6 green subjects should turn the name in Green, Student having 3 or 4 Green subjects, should turn his name Yellow

and lastly, 1 or 2 subjects Green, should turn student name into RED...

Is this something achievable?

 

[DRSteele]

I think you meant >80 is green, 40-80 is yellow and <40 is red. So this might do it. Select A2 through A7|Conditional Formatting|New Rule|Use Formula|Format Values...|paste this in =COUNTIFS($B2:$G2,">=80")>4 and the desired format to green|selct OK. Then do new rule just like that one but paste this in =OR(COUNTIFS($B2:$G2,">=80")=3,COUNTIFS($B2:$G2,">=80")=4) and the desired format to yellow. Repeat creating new rule =OR(COUNTIFS($B2:$G2,">=80")=1,COUNTIFS($B2:$G2,">=80")=2) and the desired format to red.

Excel 2012
	A	B	C	D	E	F	G
1	Student	ECON101	MATH101	ENGL101	STAT101	WOMN101	PSYC101
2	Abe	82	100	64	90	85	90
3	Bill	38	65	90	51	51	73
4	Carl	81	94	38	90	40	70
5	Dave	45	75	47	78	45	91
6	Elon	66	38	52	91	75	51
7	Fred	93	41	56	72	32	46
Sheet19