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.
ROW ($Amount $electric Nov),
COLUMN($Amount $electric Nov),
COLUMN($Amount $electric Nov)
Note that this goes quite gracefully past non-numeric entries to give a reliable running total.
I hope this helps someone out there!