# How can I create a running total in Apple Numbers (4.0ff)?

A typical use of a spreadsheet is to create a running total, as for a checkbook register. The usual way to do this is to simply add a current row to a prior row. My example is a variation -- showing the accumulation of bills over time.

In text, the formula for "Amount" in row "Homeowners" is

```   (\$Amount Homeowners) +  IF (ROW() = ROW(\$Amount \$electric Nov), 0, Calculate via Prior Sum electric Nov)
```

However, things get a little sticky if you want to interpose some notes (cell background red here) or whatever:

It fails in either column, and ruins all successive values

The way I got around this today was to generate a range dynamically and use the `SUM` operation on it. `SUM` ignores non-numeric entries:

The formula I use sets a fixed initial cell to start the running total, column "Amount" in row "electric Nov". I can't use regular tricks, I need to generate the range dynamically for each row using `INDIRECT`. This requires I create a string identifying the range to sum up. This range should only go up to the current row (which is what makes it a running total). I don't want to use fixed columns A, B, C, etc., which break if I need to shift columns around; I need to use the initial cell with the numeric column number, and I must use the "R1C1" cell notation instead of "A1".

So I concatenate the (unchanging) row and column numbers from the initial cell, a colon (:) to indicate a range, and then the current row number and column number. I have pretty-printed the formula (at least I think it's pretty!) below.

```    SUM (
INDIRECT (
CONCATENATE (
"R",
ROW (\$Amount \$electric Nov),
"C",
COLUMN(\$Amount \$electric Nov),
":",
"R",
ROW(),
"C",
COLUMN(\$Amount \$electric Nov)
),
FALSE
)
)
```

Note that this goes quite gracefully past non-numeric entries to give a reliable running total.

I hope this helps someone out there!