+ Reply to Thread
Results 1 to 8 of 8

SUMIFS / ARRAYS for a Bill Calculator sheet.

  1. #1
    Registered User
    Join Date
    09-23-2011
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    23

    SUMIFS / ARRAYS for a Bill Calculator sheet.

    Hi EXCEL GURUS!

    Good evening. I would like to ask for your expert problem solving for the sheet I'm making to automate and reduce the time I spend on calculating bills.

    Here is the scenario:
    - There are a number of PERSONS (P1 to P5) who regularly splits the bills.
    - On daily basis, they would have a group consumption that would be paid by any one of them. (e.g. Day 1 was paid by P1)
    - Some days will be divided between some of the persons only (e.g. Day 2 was paid by P2 but only P1, P2 and P3 will divide the bill.)

    On day 1 - it shows P2 to P5 need to pay P1 an amount of "20" each.
    On Day 2 - it shows P1 and P3 need to pay P2 an amount of "5" each.

    What i want to achieve:
    On the BALANCE TABLE, I am trying to compute and balance the amounts paid.
    I want to show how much each person has to collect or pay (in negative value) to the other persons. Please ignore the formulas and values mentioned and I marked red. This is where I got really confused.

    The values I want to achieve is the following:
    C12 = 0, because P1 doesn't need to pay or collect any amount to/from himself
    C13 = 25 - 7 = 18

    25 is the sum of the amounts paid by P1 for P2, shown in cells C5 (20), O5 (2) and U5 (3)
    7 is the sum of the amounts paid by P2 for P1, shown in cells E4 (5) and M4 (2)

    Basically I just want to see who owe what amount after all the bills has been entered in the sheet.

    HELP..
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 09-22-2019 at 08:48 AM. Reason: Removed unnecessary title parts - We know you are looking for help

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,722

    Re: NEED HELP with SUMIFS / ARRAYS for a Bill Calculator sheet.

    Take a look at this file with helpcells and the sumproduct formula
    A did a quick check but check please more if the results are what you expect.
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Registered User
    Join Date
    09-23-2011
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: NEED HELP with SUMIFS / ARRAYS for a Bill Calculator sheet.

    Hi Popipipo,

    Thank you. I think that's the result i was trying to achieve and just got lost somewhere.

    Thanks for the time, I am checking and trying to understand the formulas you have put in, because the sample I gave will be scaled up to 31 Days with 34 different persons.

    I will try it.

  4. #4
    Registered User
    Join Date
    09-23-2011
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    23

    Question Re: NEED HELP with SUMIFS / ARRAYS for a Bill Calculator sheet.

    Hi Popipipo,

    I tried to scale up the solution you've provided.

    Now I have 3 sheets in the workbook and the days are maxed to 31 and the persons are 40.

    I managed to fill the formulas up to 2nd sheet, but in the 3rd sheet, is there a way that the formula can be filled by dragging? or do i have to manually enter it on each cell?

    BTW, I understand your formula - thanks a lot.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,722

    Re: NEED HELP with SUMIFS / ARRAYS for a Bill Calculator sheet.

    I think you have to do it manually

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,722

    Re: NEED HELP with SUMIFS / ARRAYS for a Bill Calculator sheet.

    This way with 40 persons and 31 days is very inefficient
    40 persons 31 days are 40*31=1240 payments!!!

    Take "a box" and let each person pay or get money out of "the box" then there are only 40 payments
    Attached Files Attached Files
    Last edited by popipipo; 09-22-2019 at 07:18 AM. Reason: new attachment

  7. #7
    Registered User
    Join Date
    09-23-2011
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: NEED HELP with SUMIFS / ARRAYS for a Bill Calculator sheet.

    Quote Originally Posted by popipipo View Post
    This way with 40 persons and 31 days is very inefficient
    40 persons 31 days are 40*31=1240 payments!!!

    Take "a box" and let each person pay or get money out of "the box" then there are only 40 payments
    I know, but that's the scenario I am currently facing. Where 1 guy will pay today, the next day could be the same person or another one.

    And the bills/receipts will be marked only with "who paid" and "who's included" in that bill. And these bills are computed on a monthly basis.

    I'm working the the scaled-up based on your formula, so far it is working and I am getting the results I want to achieve. I will post it here once it is done, in case someone with same requirement needs it.

    P.S. I don't get the box idea, how would it work?

  8. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,722

    Re: SUMIFS / ARRAYS for a Bill Calculator sheet.

    The box mean you don't pay person to person
    There is a count of how much you have payed this month and how much you should have payed.
    If you have payed not enough then you payed that amount to "Person A" (the box).
    If you have payed to much you get money from that "Person A"(the box)

    Everyone does this way only one payment or get one payment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1