Help with Aging Report
 

Today is May 25, 2005

Column A has invoice numbers: 11233 , 11234 , 11235 , 11236
Column B has invoices dates: 3/24/05 , 2/24/05 , 1/24/05 ,
12/24/04
Column C has invoice amounts: $100.00, $200.00 , $300.00 , $400.00

Row 120 has titles: 31 - 60 , 61 - 90 , 91 - 120 , 120+

Row 121 totals: $100.00 , $200.00, $300.00 , $400.00


Row 121 has totals based on the invoice date and the age of the invoice
in reference to todays' date.

What I want to achieve is that if Column B has a date (s) that is 31 -
60 days older then todays' date, the corresponding amount (s) in column
C will be added together and the total would appear in Row 121 under the
31 - 60 title. And so on with the 61-90, 91-120 and 120+.

This is hard to explain, I hope this helps. Thanks - Kimberly


--
KimberlyL
------------------------------------------------------------------------
KimberlyL's Profile: http://www.excelforum.com/member.php...o&userid=23682
View this thread: http://www.excelforum.com/showthread...hreadid=373548

Do the following:
I entered your sample data in columns A to C, and rows 1 to 4.
Enter todays date in cell G1.
In column D, next to the values in coulumns A, B, & C, enter the
following formula:
=$G$1-B1
and drag down for all the rows in column A. This is the number of days
from the invoice date.

Enter the following figures in cells A118 to D118:
31, 61, 91, 121
And in A119 to D119
60, 90, 120, (last cell should be blank)

in row 120 cell A120 enter formula:
=A118&" - " &A119
and drag to your right till column D

In row 121, cell A121 enter formula:
=SUMPRODUCT($C$1:$C$4,--($D$1:$D$4=A118),--($D$1:$D$4<=A119))
and dragto copy to your right till D121

That should give you the required result

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373548

Thank you, thank you, thank you! My life (at work) is now so much
easier! -Kimberly


--
KimberlyL
------------------------------------------------------------------------
KimberlyL's Profile: http://www.excelforum.com/member.php...o&userid=23682
View this thread: http://www.excelforum.com/showthread...hreadid=373548