yogi_Array Formulas For Row By Row Cumulative Results For Columns E F G H And I

Google Spreadsheet   Post  #1971

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-13-2015

post by  Liuba:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ufdJB78Vc1I 
How to convert a SUM(FILTER() into an ARRAYFORMULA
Dear Guys,

I´ve tried to use some solutions of similar problems discussed here, but honestly, no success. So, I´ve finally decided to ask for your help.

I´ve got this table below registering payments of clients. Every client has an "account", so a client can pay more than charged and the extra money is added to his or her account.  A client can also pay less than charged and pay off later.

Columns E, F, G show the sum of what is charged, paid, and available on the account of a client from the beginning of the table up to the current row.

You track step-by-step changes of accounts.

Columns H, I, J show similar sums, but from the beginning of the table to the very end.

Whichever row you look at, you see the actual status of accounts.

Link to the table: https://docs.google.com/spreadsheets/d/11kgdW79rSOcCrBeTDGy5rSVkEOiBwsINnQ2ehDMJfJo/edit?usp=sharing

At the moment I use SUM(FILTER) formulas in red headed columns (E, F, H, I):

Column E is the sum of what has been charged to a client up to this row:

=SUM(FILTER($B$2:$B2,$D$2:$D2=$D2))

=SUM(FILTER($B$2:$B3,$D$2:$D3=$D3))

=SUM(FILTER($B$2:$B4,$D$2:$D4=$D4))

Column F is the sum of what a client has paid up to this row:

=SUM(FILTER($C$2:$C2,$D$2:$D2=$D2))

=SUM(FILTER($C$2:$C3,$D$2:$D3=$D3))

=SUM(FILTER($C$2:$C4,$D$2:$D4=$D4))

Column H is the sum of what has been charged to a client from the beginning and even after this row:

=SUM(FILTER($B:$B,$D:$D=$D2))

=SUM(FILTER($B:$B,$D:$D=$D3))

=SUM(FILTER($B:$B,$D:$D=$D4))

Column I is the sum of what a client has paid from the beginning and even after this row:

=SUM(FILTER($C:$C,$D:$D=$D2))

=SUM(FILTER($C:$C,$D:$D=$D3))

=SUM(FILTER($C:$C,$D:$D=$D4))

Columns G and J were easy to convert into ARRAYFORMULA and show the difference between what was paid and what was changed.

From other tutorials I understood I cannot use SUM formulas in ARRAYFORMULA. Well, stubbornly, I´ve tried and the result is either "0" down the whole column or the sum of the whole column in each cell...

I would greatly appreciate if you explain me what I should do to make an ARRAYFORMULA for columns E, F, H, and I, taking into account that the list of clients is constantly growing, so every time we sum up data, we need to refer to the name of a client in this particular row.

Thanks in advance!

Sincerely,

Liuba

------------------------------------------------------------------------------------------------------------------------