CODEWITHSUNDEEP
google-sheetsformulas
Kiame
Kiame

Reputation: 51

Sum above cells ignoring blanks

I have a spreadsheet where I have data from a bank account. Each bank transaction has a date and an indication if that transaction is already done or if it's just expected. When it's already done, it must be added to the total balance up to date. If not, then the total balance up to date must be blank. I need to autofilter the data, so I can filter and order it depending on date or other conditions, that's why I've been using this formula:

=IF(D3="Y";B3+INDIRECT(ADDRESS(ROW()-1;COLUMN()));"")

Problem here is that when the cell above is blank, total sum resets and it starts from the value of that transaction. I need a formula that ignores the upper blank cells, and sums all cells above that are not blank plus the amount of that transaction.

Besides, once I change the "N" in "Done" Column to a "Y" I need the formula to update and show the correct balance.

I share an example sheet for better understanding https://docs.google.com/spreadsheets/d/1_gk0YaziUhOZfRbrlfHizMrVu6OT7njIaTUyQaE6Lbs/edit?usp=sharing

Upvotes: 1

Views: 1166

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

Ok I THINK I understand what your going for - please let me know if I am confused, but I added an example on your sheet.... basically what I ended up doing was including one of your conditionals, but then also adding another function to exclude the blank rows by way of filter , index and counta It looks more complicated than it is because I nested it all back into one formula:

=IF(I3="Y";sum(G3;index(filter(indirect("F2:"&address(row()-1;column();4));ISNUMBER(indirect("F2:"&address(row()-1;column();4))));counta(filter(indirect("F2:"&address(row()-1;column();4));ISNUMBER(indirect("F2:"&address(row()-1;column();4)))))););)

  • To work it from the inside out - the way I am excluding the blank rows is by using FILTER to get all the rows from the first row with a value ( Like A2 in your example) and using INDIRECT and ADDRESS to end the array I want to include exactly one cell above the current cell.

  • Then I use the condition that the range I built has a number value in it, there fore excluding the blanks.

  • In order to get the last value available, I use COUNTA to find out the total rows in the filter, then wrap the formula with INDEX to use the counta value as the row to return (which automatically is the last row available above the current cell)

enter image description here

Upvotes: 1

Ed Nelson
Ed Nelson

Reputation: 10259

Try this in A3 and copy down:

=IF(D3="Y";B3+INDIRECT(ADDRESS(ROW()-1;COLUMN()));A2+0)

If you want to display the "N" rows as blank, add a column (B) fill in the header and the starting number (5000) then put this in B3:

=if(E3="N";"";A3)

Copy it down then hide column A.

Upvotes: 1

Related Questions