Extended Subtraction Between Columns on same row in area

Question

I want to calculate the transaction value so by that i mean i've got different accounts in column "A" and for each transaction i got a column like "B".

The transactions show the new value, after the transaction, for two accounts.

And i need a generic function to calculate the "transaction value", something like Ai - Bi … Ai+1 - Bi+1 …

or as a function (pseudocode), something like this:

int res = 0; foreach (int i = 1; i < 4 (rows); i++) { if (Bi != null && Ai > Bi) { res = Ai - Bi } }

Example:

          A                B (transactions)
    ------------------------------------------------------------
1   |  $ 100     |                                             |
    ------------------------------------------------------------
2   |            |                                             | 
    ------------------------------------------------------------
3   |  $ 200     | $ 150 (now, after transaction)              |  
    ------------------------------------------------------------
4   |  $ 300     | $ 350 (now, after transaction)              | 
    ------------------------------------------------------------
5   | sum(A1:A4) | (A1-B1)+(A2-B2)+(A3-B3)… (transaction value)|
    ------------------------------------------------------------
=>  | $ 600      | $ 50                                        |
    ------------------------------------------------------------

So i transferred $50 from Account A3 to A4.

I mainly need the logic behind this

Any help is very much appreciated :)

Answer 1

Two formulas:

SUMPRODUCT:

=SUMPRODUCT((B1:B4<A1:A4)*(B1:B4<>"")*(A1:A4-B1:B4))

Or this array formula:

=SUM(IF((B1:B4<A1:A4)*(B1:B4<>""),(A1:A4-B1:B4)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

If the cells are populated with formulas that return a "" then use the second as the null string will cause an error with the SUMPRODUCT formula. If they are truly empty then either will work.