I'm trying to implement a more elegant than normal way to maintain a running total using SUMIFS, but can't get it to work.
The standard way to do this is simple and effective. If the items I'm summing are in column C and I want a running total in column D, I simply enter =C1 in D1, and =D1+C2 in D2, copying this formula all the way down.
The problem comes when I want to insert a row in the middle of this sequence. Inserting a new row 7 will move the previous row 7 down to row 8, which is fine, but the formula in cell D8 will still refer to D6, when I want it to refer to D7, which will hold the running total for the newly entered row.
Obviously it's no big deal to fix this on an ad hoc basis for a single edit, but I'm working on an application that calls for many such insertions, and I had the idea that I could eliminate this issue by using SUMIFS for the running total: just make it add up all the entries in column C if they're on a row numbered the same as or less than the current row. I can't get it to work, though.
Here's an example of what doesn't work:
=SUMIFS($Y$2:$Y$22,$R$2:$R$22,"<=R3")
I'm trying to create a running total of the numbers in column Y (the first range) by creating a condition where an item is added only if the value in column R (which could be the row numbers or any other sequential numbering of the data items) is less than or equal to the value in column R for the current row. It doesn't work because R3 is within the quotation marks, but it also doesn't work without the quotation marks because they're needed when the condition includes <= symbols. Am I missing an easy way to do this?
Bookmarks