Hi all
I have a Ageing report for supplier wise payment based on over due and i need to sum in P4 column over due based on days in (C column days)
if payment days 15 need to sum from 15 to 30 days to (> 180 days )
if payment days 30 need to sum from 30 to 45 days to (> 180 days )
| Days | Function |
| 15 | 1 to 15 days |
| 30 | 15 to 30 days |
| 45 | 30 to 45 days |
| 60 | 45 to 60 days |
| 90 | 60 to 90 days |
| 120 | above 90 |
Hello,
I find it difficult to follow the logic here, you want to sum something overdue in P column. How do you know if it is overdue when there are no dates? You have number of days in C column, and what seems to be a lookup table, but what you give as examples in your description does not match the lookup table. And lastly, columns D to O, what are they?
Would be great if you can elaborate and give examples of expected results.
Br,
Anders
Hi Mr. Anders
actually we are getting over due details from in our Tally ERP, we are identifying over due in "C" column like in a days (15,30,45,60,90,120), based on that we are summing in over dues column "P".
Column "D" -- we summing value from E column to O Column
Column "O" is the greater 180 days
Your logic:
You want to keep track of every date until 120 days overdue, at least that's what you small table tells us.
The overdue dats above 90 will then, I assume be calculated based upon the current date?
Where is the date you base your calculation on?
What is the conten of the column OverDue its a sum of the entire row? or What?
There is no sigle mention in the table of the control date, just a tally of days, what do you want?
And not less important. WHICH VERSION OF EXCEL?
I understand that you get the ageing report (columns A to O) from your ERP system. The file you uploaded is for week number 43. It's static information based on the date when you run the report.
Column C holds the payment term for each customer and column D the total amount outstanding. The split across the various ageing columns is done in the ERP system. No Excel formulas needed here. Then, you want to sum columns F through O for amounts that are older then the payment term (i.e. over due).
In the attached file I've added a SUMIFS formula in column P that does that. Note however that I also changed the column headers to numbers representing 'up to that number of days'. That is, 15 means 'up to 15 days old'. Much easier to calculate with numbers 15, 30 etc. than with a texts "1 to 15 days", "15 to 30 days" etc.
Is that what you needed?
Hi Riny
I am very happy its working 100% fine. thanks you soo much.
Regards/Saliha
Nice one Riny
