Conditional formatting 2 sum cells

[smithl8686]

Hello, 

I have a spreadsheet that tracks clinical supervision hours. One column sums individual supervision (F55) and the other group supervision (G55). There is a limit with group supervision hours (100 hours), so I have conditional formatting turn red when the cell hits 100 hours and I use the MIN formula so it stops tracking. The total supervision requirement is 200 hours and conditional formatting for the individual supervision column (F55) is set to turn green when it hits 200 hours. I'd like the individual supervision column (F55) to turn green once the combination of the individual (F55) and group columns (G55) totals 200. Right now the individual column turns green only when it hits 200 hours, which doesn't make any sense if you're doing a combination of individual and group supervision. 

Hope that was understood. Thanks for your assistance! 

[#2]

@smithl8686  You want select the range, then select custom formula and enter = ($F55+$G55)>=200  to do this:

1) highlight range (is it just cells F55 and G55 or maybe F55:G100. either way make sure the top left is the 'active' cell and I will assume that is F55)

2) select conditional formatting and select 'New Rule' and then choose "Use a formula to determine which cells to format" from the upper window

3) In the lower window enter =($F55+$G55)>=200 into the formula bar 

4) Click Format and choose how to highlight/format the cells

Note if the whole column F and column G are full of those types of values then select the whole columns/range (lets assume F1 is now the upper left corner and active) then follow steps 2-4 but use =($F1+$G1)>=200

[#3]

@m_tarler that worked! Thanks! 

Now, what if I want G55 to still turn red when I reach the max 100 hours of the group supervision and F55 to turn green once the other 100 hours are reached? So, it'll still go off the sum of those 2 cells but turn different colors for different amounts. 

[#4]

those are separate conditonal format rules. and if needed you can move rules up or down or select 'stop if true' to make 1 rule 'override' another rule

wait after re-reading it all I THINK you want the above 'special' rule to be applied to ONLY F55 (or column F) the formula is the same but you only want the cells in column F (or only F55) to apply to that sum rule.

[#5]

So I could just follow the steps that you gave me and just apply to F55? G55 is already set to turn red and stop calculating after 100 hours.

[#6]

yes. see above edit to my last comment (my edit crossed the ether with your reply. lol)

[#7]

Ha! I got it! Thanks so much for your help!!