Conditional Formatting with Multiple Rows

[Spreadsheetz]

Hi There,

Hoping this is easier than I'm making it..

I'm trying to conditionally format columns based on the sum of multiple rows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Jeff[/TD]
[TD]John[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The formatting I'm looking to see is: if row2+row3>2, then green; if row2+row3=2, then orange; if row2+row3<2, then red.

I would imagine it's easy to create a conditional formatting formula to do this, but I cannot figure out how to have a sum of columns apply to a condition in this way. Any help would be appreciated!

 

[dreid1011]

Perhaps this:

Select your entire range, then make these three CF rules:

=SUM(A$2:A$3)>2 format green
=SUM(A$2:A$3)=2 format orange
=SUM(A$2:A$3)<2 format red

 

[Marcelo Branco]

You need 3 different rules

Select A1:C3 being A1 the active cell


Rule 1 (green)
Use this formula in CF
=SUM(A$2:A$3)>2
Format button and pick format: Fill --> green


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD="bgcolor: #92D050"]

Jeff​

[/TD]
[TD="bgcolor: #92D050"]

John​

[/TD]
[TD]

Jack​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #92D050"]

3​

[/TD]
[TD="bgcolor: #92D050"]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD="bgcolor: #92D050"]

3​

[/TD]
[TD="bgcolor: #92D050"]

3​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


Do the same for the others rules adjusting the formula

Hope this helps

M.

 

[Spreadsheetz]

This is super helpful - thanks!!

I'm almost where I need to be.. 2 questions though.

1) When I do this, the cell in row 2 is the only cell which takes on the condition color. I have the entire column selected, and the show formatting rules option is set to "current selection." Any idea how to make this apply to the whole column?
2) Is there a quick way to apply this condition to many rows? Or is it a matter of manual entry per column that needs the formatting?

Thanks!

 

[dreid1011]

You can apply this to as many rows/columns as you choose. Just make sure you select the whole area before you create/apply the rule. I'm not sure about 1) though. When I tested the formula, it highlighted all the cells in the column(s) that matched the criteria.

The only thing I can think of for 1) is that you didn't have the entire column/rows selected that you want to apply the rule to.