Designing A Sheet

What I need the sheet to do:

1.Rows containing; Costumers name, invoice number, date of service, subtotal, tax, total, and whether or not they have paid yet.

2.I then need to take that data and group it to see how much each person still owes, which individual bills they have paid, and which individual bills they have yet to pay.

3.Then take all that data from each separate bill statement, and make an account balance for each person he does work for.

I had already done this for over a year’s worth of work he did. But after all the data was in, for the life of me I couldn’t think of a way to do step 2-3. The problem is that on some jobs he does, the people will pay him upfront, in which case he marks paid on the bill. But other times they will send him a check in the mail 1-3+ months later and some times not for the full amount they owe. Now I have a list of all those checks he receives, but I can’t find a simple way to subtract those checks from what people still owe him.

I need a way to sub-total each customers bills, but still be able to add in payments, or future bills and payments that person may have. Each month he does more work, and sometimes for the same people, so I need to be able to just sit down, look at each bill, and just type in the data I need off it into each person’s separate account.

The only way I could think to do this, is to give each different customer he has their own work sheet. But this makes the file size huge and organizing totals a pain. Not to mention editing the data in individual accounts for future work/payments a pain.