Conditional Format - adding several data cells for one condition

[JerBear]

Hello. I am trying to use conditional formatting and combine the value of three cells, and based on the total of the cells, highlight all the cells with a fill color. Example:

A4 = 450
A6 = 150
A8 = 350

If the total of the cells is greater than 700, then I want all cells to be highlighted in red. If the total value of the cells is less than 300, then I want all cells to be highlighted with green. If the total value is between 301 and 699, then I want all the cells to be highlighted with yellow. (There is a reason for no data being in cells A5 and A7 - these are blank and will always be blank)

I tried using a formula "=SUM(A4:A8)>700" for one condition, but the result is cell A4 is the only cell highlighted with red. What formula (or formula's) do I use so all three cells are highlighted?

Thanks!

[Neil_]

"=SUM($A$4:$A$8)>700" If you look at the other cell's conditional formats, they will probably have different ranges as your formula is relative, not absolute.

[JerBear]

Thanks Neil. I'm not sure what you mean by "relative" versus "absolute." And how would I change my formula to reflect an absolute change? Thanks!

[FDibbins]

Hi and welcome to the forum

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =sum(A4:A8)>700 format fill red
repeat 3&4 with =sum(A4:A8)<300
repeat 3&4 with =and(sum(A4:A8)>=300,sum(A4:A8)<=700)

you state that you want it less than 300, greater than 700, and from 301 to 699 - what about exactly 300 and 700? I included the >=300 and <=700 onthe 3rd rule, move it to where you want it

let me know how you make out please?

[JerBear]

Thank you - the first cell (450) is highlighted in red while the other two cells are highlighted in yellow. (And I used your "greater than" and "less than" rules as those are truly what I'm looking for.)

[JerBear]

This is SOLVED!

I used an IF statement to solve my problem. I eventually used "=IF(A4+A6+A8>700,1,"")".

Thanks everyone!