Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to change the background of a cell as long as the running total in that column from start to that cell is less than a limit specified in a different cell.

Conceptually, the formula should look like this:


or, in more concrete terms:


For example, suppose the value in cell F10 is 19, and column C looks like this (starting from C6):


Now, for illustrative purposes, let's compute the running total for each cell in the column (note that this column does not actually exist in the spreadsheet):


Remember, I want to change the background if the running total is less than the specified limit. So, for this example, the first 3 cells should have their backgrounds changed.

My problem is that I don't know how to write the reference for THIS_CELL. This is what I've tried so far:


...but I get this error:

error dialog

Any suggestions on how I can make this work the way I want, or is that error message indicating that it's impossible? I know I could cheat by adding a "running total" column and configure conditional formatting based on those results, but I want to avoid that if at all possible.

share|improve this question
up vote 1 down vote accepted

You just need to set your cell references carefully in the formula for formatting the sum column. Assuming that the sum values start in cell A1, highlight the sum column values and set the formula for conditional formatting as:

share|improve this answer
You're right; I was making it harder than it needed to be. You got me pointed in the right direction, then I realized it was always off by one cell so I actually needed =SUM($A$1:A1)-A1<$F$10. Thanks! – rob May 17 '13 at 2:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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