Running Sum

[WayneLambert]

This formula uses Excel's R1C1 notation so that a formula is written in the same way all the way down. This means that if the data is sorted, it still works! Excel needs to be in the default A1 notation for this to work. If Excel is in R1C1 mode, the INDIRECT formulas are not necessary.

To sum a range without any conditions:

=SUM(INDIRECT("R[0]C[-2]",FALSE))

The zero is square brackets is actually optional since we are not offsetting any number of rows. It could therefore also be written as:

=SUM(INDIRECT("RC[-2]",FALSE))

To sum a range with conditions whilst in A1 notation:

=SUMIFS(INDIRECT("R2C[-1]:R[0]C[-1]",FALSE),INDIRECT("R2C[-2]:R[0]C[-2]",FALSE),INDIRECT("RC[-2]",FALSE))

The zero is square brackets is actually optional since we are not offsetting any number of rows. It could therefore also be written as:

=SUMIFS(INDIRECT("R2C[-1]:RC[-1]",FALSE),INDIRECT("R2C[-2]:RC[-2]",FALSE),INDIRECT("RC[-2]",FALSE))

Shorthand versions of the above formulas where in R1C1 mode:

=SUM(RC[-2])
=SUMIFS(R2C[-1]:RC[-1],R2C[-2]:RC[-2],RC[-2])