Conditional Formatting Based on Multiple Rules

Question

I'm trying to create a multi rule custom formula for conditional formatting purposes in Google Sheets.

Here is what I want to achieve -

If Deal Size is Small, the deal length cell should be highlighted a specific color based on the criteria -

• 0-35 Green

• 35-40 Yellow

• 40+ Red

If Deal Size is Large, the deal length cell should be highlighted a specific color based on the criteria -

• 0-45 Green

• 45-55 Yellow

• 55+ Red

Here is some sample data -

Date Deal Started	Date Deal Ended	Deal Length	Deal Size
1/13/2023	1/25/2023	12	Small
12/8/2022	1/17/2023	40	Large
11/8/2022	1/9/2023	62	Small
10/7/2022	1/31/2023	116	Large
12/12/2022	1/30/2023	49	Large

I've read some blogs online but haven’t found a solution for what I want to achieve.

Answer 1

0-35 for green and 35-40 for yellow. So what color if exactly 35?

Use 3 custom formulas for conditional formatting. Move = signs where needed. Range in example C2:C7

Green:

=OR(AND(D2="Small",C2>0,C2<35),AND(D2="Large",C2>0,C2<45))

Yellow:

=OR(AND(D2="Small",C2>=35,C2<40),AND(D2="Large",C2>=45,C2<55))

Red:

=OR(AND(D2="Small",C2>=40),AND(D2="Large",C2>=55))

Result: