Conditional formatting in Excel 2016 based on the sum of other cells

0
votes

I am trying to create conditional formatting in a single cell (red for negative and green for positive) based on the sum of three other cells.

I.e. Trying to fill cell K3 in green but ONLY if the value in K3 is greater than the sum of D3:D5. And fill in red if the value of the cell is less than the sum of D3:D5.

Thanks

1
excel-formula
Pretty basic question, did you do any research on conditional formatting? There's a bunch of questions (on this site and otherwise) about conditional formatting that can help you out. Here's one example. – ImaginaryHuman072889

1 Answers

0
votes

Select K3, clear it of any existing CF attempt and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=K3>SUM(D3:D5)

Format..., select green formatting, OK, OK.

Then add another rule:

=K3<SUM(D3:D5)

for red formatting.

This leaves K3 with no CF applicable where K3 equals the sum of D3:D5. If you can accept that condition is either red or green, at your choice, a CF rule could be saved by applying 'standard' formatting for the 'other' condition.

Alternatively, something like this:

SO49564741 example