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 -

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

CodePudding user response：

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:

CodePudding user response：

or you can do it like this too:

green:

=((D2="Small")*(C2>0)*(C2<35)) ((D2="Large")*(C2>0)*(C2<45))

yellow:

=((D2="Small")*(C2>=35)*(C2<40)) ((D2="Large")*(C2>=45)*(C2<55))

red:

=((D2="Small")*(C2>=40)) ((D2="Large")*(C2>=55))