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

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

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
---

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

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