Skip blank cells until value added in Google Sheets

1
votes

I've created a budget sheet that includes a running balance in column E.

The problem that I've run into is my formula =IF(C140="","",(SUM($E139,C140))) works down the line after E140 (first day of the month that I started to implement this) until there is a blank. At the blank, the formula breaks for me. Now because this is a projected running total, it's preferred to have the blank spaces to allow for the days of the month.

I've tried a couple different ways to get the isblank or lookup formulas to work but I realize this is beyond my excel and google sheets knowledge. Is there a way to get the formula to skip the blank cells down column E and just use the last value, ie $598.66 in E143 when you get to the next cell that has a value in it, ie E151?

Photo added for reference....

enter image description here

1
if-statementgoogle-sheetssumarray-formulascumulative-sum
Yes there is. Using Google Apps script. you could create a custom formula that skips the blanks. But you do have to program the logic into it. (eg. When to top?) – Aerials
share a copy of your sheet – player0

1 Answers

1
votes

try:

=ARRAYFORMULA(IF(E2:E="",,MMULT(TRANSPOSE((ROW(E2:E)<=
 TRANSPOSE(ROW(E2:E)))*E2:E), SIGN(E2:E)^0)))

0