# Color row until the sum of cell is equal to a certain value

Good day everyone, I need to dynamically color rows of a table, until the sum of cells of a column reachs a certain value The "Effort" column is column E. I need therefore need to color rows until the sum of column E value is 240. In this case, assuming that "Feature O" cell is in row 4, I would need to color until row 14 "Feature E" excluded. This table is subject to changes, as new rows might be added, or effort recalculated.

I hope it is clear enough so that you can help me.

Cheers. Tommaso

New contributor
Tommaso is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

## 2 Answers

Here is an example on the assumption you need to color the cells unless the total sum would go above `240`: • Select range `E4:E16`
• New conditional formatting rule by formula:

``````=SUM(\$E\$4:E4)<=240
``````
• Choose format and apply

Now upon editing or adding: • Works like charm! Thanks! – Tommaso Aug 14 at 12:25
• @Tommaso, glad it works, kindly upvote/accept as answer as described here if this has been usefull. – JvdV Aug 14 at 12:28
• Done, but unfortunately I don't have 15 reputation, so my vote is not properly shown T.T – Tommaso Aug 14 at 12:33
• @tommaso. Quite certain you don't need 15 reputation to accept answers (green tick next to the answer) – JvdV Aug 14 at 20:20

Since you are using TABLE therefore my approach is quite different to highlight rows, until the SUM is equals to certain value. How it works:

• You need to create one Helper Column.
• Enter Criteria `240` in Cell `N1`.
• Formula in Cell `N2`, fill it down:

`=SUM(INDEX(Table1[Efforts],1):Table1[@Efforts])<=\$N\$1`

• Select entire Range `I2:N14`.

• Reach to Conditional Formatting, New Rule and apply this formula.

`=\$N2=TRUE`

• Apply an appropriate Color and finish with Ok.

• Then click Conditional Formatting then Manager Rules.

• Now using Applies To Combo box, select Range `J2:J14`, then hit Apply & finish with Ok. • You get the effects on `Column J`.

N.B.

• Using Cell `N1` to match with criteria makes the exercise dynamic.

If next is to highlight rows, until the `SUM` is equals to `14` or any value, then instead of redraw entire exercise, only replace the match value in cell `N1`, which saves the time too. • Later on for neatness you may hide Range `N2:N14` as well as adjust other parameters also.