# Excel - accounts payable aging report

Asked By John Grinde on 09-Dec-07 12:28 PM
```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```

demechani replied on 09-Dec-07 09:13 PM
```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
---```
demechani replied on 10-Dec-07 06:49 AM
```Slight errata to the labels indicated in the earlier formulas:

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---```
JohnGrinde replied on 10-Dec-07 10:33 PM
```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 replied on 11-Dec-07 01:51 AM
```welcome, John.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---```