*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

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