# SUMIFS / ARRAYS for a Bill Calculator sheet.

1. ## 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..

2. ## 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.

3. ## 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. ## 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.

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

I think you have to do it manually

6. ## 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

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

Originally Posted by popipipo
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. ## 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.

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