New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby danymason » Tue Mar 15, 2016 7:29 am

hi;

First thanks for your time in checking on this thread to help.

id like to work out column k that deals with (total payments of each of the customer that i am getting via sumif formula and it keeps on updating periodically). i have manually done column k so i am wondering is there a way to nicely get this done using a perfect formula.

This is actually total payments - individual invoices and also, if yes can this be termed as paid or not paid considering the paid amount is greater than the billed amount.

Reference sheet attached that contains data. please feel free to ask questions and big thanks in advance for your help!...
  • 0

You do not have the required permissions to view the files attached to this post.
Last edited by danymason on Tue Mar 15, 2016 8:30 am, edited 1 time in total.
danymason
Rookie
 
Posts: 6
Joined: Mar 15, 2016
Reputation: 0
Excel Version: Excel 2007

Re: HOW TO CATER INDIVIDUAL INVOICES TO MARK PAID OR NOT PAID BASED ON TOTAL ALLOCATION CUSTOMER WISE

Postby pecoflyer » Tue Mar 15, 2016 8:19 am

Hi and welcome to the board

Please not that writing in upper case is the equivalent of shouting.

As we are all friends here, there is no need to do this.

Thank you
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby danymason » Tue Mar 15, 2016 8:32 am

@pecoflyer:
Sincere apologies, my Caps Lock messed up. Edited my post. Appreciate your candid response.
  • 0

danymason
Rookie
 
Posts: 6
Joined: Mar 15, 2016
Reputation: 0
Excel Version: Excel 2007

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby pecoflyer » Tue Mar 15, 2016 8:51 am

Not being an accountant I don't completely understand your query.
Could you please give some more explanation? Thanks
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby danymason » Tue Mar 15, 2016 9:05 am

Thanks Peco.

Sure!..

Basically, I'd like to calculate (column K) Invoice Balances.

1. I have sold a product that gives column (B)- Bill Amount.Each customer owes Bill Amount of this column.If you scroll below, you may see repeated purchases and thus the billed amount.

2. Column H is the Total amount paid by each of these customers. Accumulated using Sumif formula in other sheet.

3. Total Billed amount is just a depiction of how much in total owed by each of the customer. That for example adding all M Steve's bill amount will take you to this (Total Billed amount).

4. Total Due is a bigger picture of (Total Payments - Total Billed Amount).

5. Column K is worked out in this manner.

Total Payments ( cell H2) - Bill Amount ( cell B2). I'd like to know if I can have the balances for each of these customers accordingly.

M Steve total payments received and deducting every Bill amount so I can see if they have outstanding left in the month of N30(30 days); N60 ( 60 days ) or N 90 (90 days after product is sold).Thus appropriate status saying " Bill is paid" ;"Bill is not Paid". hope this helps. Thanks very much!..
  • 0

danymason
Rookie
 
Posts: 6
Joined: Mar 15, 2016
Reputation: 0
Excel Version: Excel 2007

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby pecoflyer » Tue Mar 15, 2016 9:32 am

In col H I see that total payments by one customer remains the same.
Is the cumulative payment from that customer the sum of those payments, or does that mean that if, say, MSTEVE pays another 100 K tomorrow,all his tot payments in col H will be 220 K ?
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby pecoflyer » Tue Mar 15, 2016 9:34 am

The total in I2 also seems wrong?
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby pecoflyer » Tue Mar 15, 2016 10:01 am

Try =H2-SUMIF($A$1:A2,A2,$B$1:B2) in L2 and drag down
  • 1

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby danymason » Wed Mar 16, 2016 4:57 am

pecoflyer

Thanks for catching mistake in i2.
Formula worked like a charm. :)... YOU ROCK!!!.. i spent countless days figuring out but surely a tough nut to crack :)

Many thanks once again. Superhelpful!.... Definitely!...
  • 0

danymason
Rookie
 
Posts: 6
Joined: Mar 15, 2016
Reputation: 0
Excel Version: Excel 2007

Re: How to cater individual invoices to mark paid or not paid based on total allocation customer wise

Postby pecoflyer » Wed Mar 16, 2016 7:04 am

You're welcome - Thanks for the rep
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Next

Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 63 guests

cron