Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
accounts payable aging report
I am trying to use the function SumIf to come up with totals for 30, 60, 90
day sub-totals for a list of invoices. Column C has the invoice dates and column Q has the outstanding balance due on the invoice. I have found that I could not get any totals when using Today() function in the criteria, I could only use the exact date for either the 30 day or the 90 day situation. I could not figure out a way to find subtotals for dates between 60 and 90. For the 30 day I am using a criteria for anything less than 30 days and then subtracting the totals for the other two sub-totals. The forumlas I have used as listed below: Thirty Day: =SUMIF($C$7:$C$200,"<11/9/2007",$Q7:$Q200) -SUM($S$7:$T$7) - where the sum for S7:T: are the sum of the other two sub-totals Sixty Day: =SUM($Q29:$Q36) - this is the only way I could get the 60 to 90 sub-total, I had to manually enter the actual range Ninety Day: =SUMIF($C$7:$C$200,"<9/10/2007",$Q$7:$Q$200) Does any one know of a solution to using Today() function in criteria of the SumIf function and also how to use a criteria that would pick dates between a range of dates? The purpose is to have a work area where specified vendors invioces can be dumped in to prepare an aging report |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
accounts payable aging report
One way ..
Put in R7: =IF(C7="","",IF(C7<TODAY()-90,"90",IF(AND(C7=TODAY()-90,C7<TODAY()-60),"=90-60",IF(AND(C7=TODAY()-60,C7<TODAY()-30),"=60-30",IF(C7=TODAY()-30,"<30",""))))) Copy R7 down to R200. R7:R200 will return all the various aging labels that you could then use in sumproduct formulas as desired, for eg: =SUMPRODUCT(($R$7:$R$200="=60-30")*$Q7:$Q200) returns the total of amounts in col Q for dates in col C within 30-60 days old -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John Grinder" wrote: I am trying to use the function SumIf to come up with totals for 30, 60, 90 day sub-totals for a list of invoices. Column C has the invoice dates and column Q has the outstanding balance due on the invoice. I have found that I could not get any totals when using Today() function in the criteria, I could only use the exact date for either the 30 day or the 90 day situation. I could not figure out a way to find subtotals for dates between 60 and 90. For the 30 day I am using a criteria for anything less than 30 days and then subtracting the totals for the other two sub-totals. The forumlas I have used as listed below: Thirty Day: =SUMIF($C$7:$C$200,"<11/9/2007",$Q7:$Q200) -SUM($S$7:$T$7) - where the sum for S7:T: are the sum of the other two sub-totals Sixty Day: =SUM($Q29:$Q36) - this is the only way I could get the 60 to 90 sub-total, I had to manually enter the actual range Ninety Day: =SUMIF($C$7:$C$200,"<9/10/2007",$Q$7:$Q$200) Does any one know of a solution to using Today() function in criteria of the SumIf function and also how to use a criteria that would pick dates between a range of dates? The purpose is to have a work area where specified vendors invioces can be dumped in to prepare an aging report |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
accounts payable aging report
Slight errata to the labels indicated in the earlier formulas:
"=90-60" should read: "60-90" "=60-30" should read: "30-60" "<30" should read: "<=30" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
accounts payable aging report
Thank you very much for you posting, I will have time tomorrow to check it
out in my app and will rate your response then. John "Max" wrote: One way .. Put in R7: =IF(C7="","",IF(C7<TODAY()-90,"90",IF(AND(C7=TODAY()-90,C7<TODAY()-60),"=90-60",IF(AND(C7=TODAY()-60,C7<TODAY()-30),"=60-30",IF(C7=TODAY()-30,"<30",""))))) Copy R7 down to R200. R7:R200 will return all the various aging labels that you could then use in sumproduct formulas as desired, for eg: =SUMPRODUCT(($R$7:$R$200="=60-30")*$Q7:$Q200) returns the total of amounts in col Q for dates in col C within 30-60 days old -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John Grinder" wrote: I am trying to use the function SumIf to come up with totals for 30, 60, 90 day sub-totals for a list of invoices. Column C has the invoice dates and column Q has the outstanding balance due on the invoice. I have found that I could not get any totals when using Today() function in the criteria, I could only use the exact date for either the 30 day or the 90 day situation. I could not figure out a way to find subtotals for dates between 60 and 90. For the 30 day I am using a criteria for anything less than 30 days and then subtracting the totals for the other two sub-totals. The forumlas I have used as listed below: Thirty Day: =SUMIF($C$7:$C$200,"<11/9/2007",$Q7:$Q200) -SUM($S$7:$T$7) - where the sum for S7:T: are the sum of the other two sub-totals Sixty Day: =SUM($Q29:$Q36) - this is the only way I could get the 60 to 90 sub-total, I had to manually enter the actual range Ninety Day: =SUMIF($C$7:$C$200,"<9/10/2007",$Q$7:$Q$200) Does any one know of a solution to using Today() function in criteria of the SumIf function and also how to use a criteria that would pick dates between a range of dates? The purpose is to have a work area where specified vendors invioces can be dumped in to prepare an aging report |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
accounts payable aging report
welcome, John.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John Grinder" wrote in message ... Thank you very much for you posting, I will have time tomorrow to check it out in my app and will rate your response then. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accounts Receiveable Aging | Excel Worksheet Functions | |||
Accounts Receivable Aging Report | Excel Discussion (Misc queries) | |||
Help with Aging Report | Excel Worksheet Functions | |||
Using Excel for Accounts Payable - Comprehensive | Excel Worksheet Functions | |||
How do you create a vendor tracking form for accounts payable pro. | Excel Discussion (Misc queries) |