Fill colors of cells using Conditional formatting based on 3 criteria

Hi,

I have a spread sheet3 with Application, Severity and Count as Column names. I want to define a table for each application and corresponding severity level and display colors based on number of cycles. If number of cycles for Critical, high issue is >1 then respective box in table fill Red Color else fill Green Color. For Medium and Low severity, if cycles >2 then display red in respective box else display Green.

Example - As you see Sony shows Medium severity with 4 cycles in row 2, So M8 should be Red, and Critical severity has 4 cycles in row 6 so format K8 with Red.

Over all task is, Ex: K8 represents Sony with Critical issues. So we look at data for Application name Sony and Severity be Critical and there corresponding number of Cycles. If any cycle value is >1 then format K8 with RED and if it is =1 format K8 with Green.

If an application has more than one same severity(like Sony has 2 Critical issues in Row 6 and Row 26) den look for all corresponding cycles and even if one cycle > 1 den turn red for High in K8.


Please help me with a solution.

@etaf- This formula does not provide the exact solution. I see we are adding up the cycles and applying conditional formatting. I rather don't want to add the cycles but compare the highest of cycle in each criteria and if the highest crosses the limit then we turn corresponding cell to Red.

Row 5 and 22 has Apple for Low severity level and both the cycles does exceed 3 (Red for >3, Green for <=3). So N9 should be Green. Can we determine the Max value rather than Sum

This Formula worked perfectly right. Thank you

So, what I meant in my first post was to find number of cycles for each criteria and even if one of them exceeds our limit then turn Red else Turn Green.

Example - As you see Sony shows Medium severity with 4 cycles in row 2, So M8 should be Red, and Critical severity has 4 cycles in row 6 so format K8 with Red.

Over all task is, Ex: K8 represents Sony with Critical issues. So we look at data for Application name Sony and Severity be Critical and there corresponding number of Cycles. If any cycle value is >1 then format K8 with RED and if it is =1 format K8 with Green.

If an application has more than one same severity(like Sony has 2 Critical issues in Row 6 and Row 26) den look for all corresponding cycles and even if one cycle > 1 den turn red for High in K8.

Hope I am clear this time.

Regards