0

Good day everyone, I need to dynamically color rows of a table, until the sum of cells of a column reachs a certain value

age

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.
3

Here is an example on the assumption you need to color the cells unless the total sum would go above 240:

enter image description here

  • Select range E4:E16
  • New conditional formatting rule by formula:

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

Now upon editing or adding:

enter image description here

  • 1
    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
0

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

enter image description here

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.

enter image description here

  • 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.

enter image description here

  • Later on for neatness you may hide Range N2:N14 as well as adjust other parameters also.

Your Answer

Tommaso is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.