Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shortening a forumula | Excel Discussion (Misc queries) | |||
How do I create a push report in Excel? | Excel Discussion (Misc queries) | |||
Linking worksheets after runnning report | Excel Discussion (Misc queries) | |||
Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) | |||
30 Day Aging Report Using Conditional Formating | Excel Worksheet Functions |