0

I have a one to many data model. specifically, a Customer table and a Payment table. I want to have the balance of each Customer in an extra column in their table.

Data Model:

Customer: Customer_ID, Name, Fee, Balance

Payment: Customer_ID, Amount

And the balance would be sum of all of the payment Amounts of the customer minus the Fee. (This is not a perfect design, but works fine for my case.)

How can I get the balance? I have tried joining two tables using "relationships", VLOOKUP, SUMIF. But either these are not the right tools or I don't know how to use them.

Is there a simple solution without using Macros?

1

A quick solution using SUMIF where the Range is the Customer_ID column in the Payment Sheet (in my example A1 to A9), with the lookup criteria being the Customer_ID in column A on the Customer Sheet and the Sum_range being the Payment Ammount(s) in column B of the Payment Sheet.

The SUMIF formula in Balance Column D2 becomes

=SUMIF(Payment!$A$1:$A$9,A2,Payment!B:B)-C2

then fill down by double clicking the bottom right hand corner of cell D2

Customer Sheet

Payment Sheet

  • thanks a lot! I replaced Payment!$A$1:$B$9, with Payment!A:A . this way I don't create a limit at the end of the table. any downsides? – Makan Tayebi Mar 22 '16 at 12:28
  • 1
    Whole Column References have performance disadvantages if you have Array formulas as this forces calculation for all the cells in the column, including empty cells as this can be slow to calculate. SUMIF is not an array formula and should automatically recognize the last used row in the column so there should not be any downside. – Antony Mar 22 '16 at 15:04
  • what would you suggest instead of a quick solution? something more robust and maintainable that I can learn for future. (in the boundaries of excel) – Makan Tayebi Mar 26 '16 at 4:59
  • SUMIF is a perfectly good solution for your requirements as described. – Antony Mar 28 '16 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.