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!