accounts payable aging report

[John Grinder]

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

 

[Max]

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]

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"


---

 

[John Grinder]

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]

welcome, John.