Finding the Maximum Amount Outstanding within a Date Range

[Met123dm]

I need help finding a formula that can tell me the maximum amount I have oustanding within a date range. Let me be more specific....

I the first column I have different amounts ($100, $200, $300, etc). In the second column, I have a start date (3/1/2001, 6/30/2001, 12/31/2001, etc.). In the third column, I have an end date (7/31/2001, 9/30/2002, 12/31/2002, etc.). What I'm looking for is a formula that can tell me the maximum amount I have outstanding at any given time -- keeping in mind that some of the start and end dates overlap. Now obviously, for only three data points, I could probably just figure the answer out manually -- in the example above the answer is $500. But, in my spreadsheet, I have about 100 sets of data and need a formula to do this.

Does anyone know of a formula that could help me?

 

[Nimrod]

=SUMPRODUCT(--(B1:B200<=G1),--(C1:C200>=G1),A1:A200)

In this example:
Col A = Amounts
Col B = Start Dates
Col C = Final Dates
Cell G1 = The criteria data you want totaled.

 

[Met123dm]

This is really helpful, thanks! But it looks like your formula forces me to select a specific date. I'd have to pick a specific date and enter it into cell G1 to get the total amount outstanding on that date.

But what if I wanted to look at the data as a whole and find out the maximum amount that is outstanding during the entire date range, rather than just on a specific date?

Thanks!

 

[Nimrod]

Met123dm:
For your the example you provided in your first posting, what Start and End dates would you use ?

 

[Met123dm]

I'd want to know the maximum amount outstanding at anytime between 3/1/01 and 12/31/02.

 

[Nimrod]

Met...
Unfortunately I could not come up with a formula that would meet your requirements. However if your willing to use a VBA solution here's one that should give you the correct results.
P.S. : You may want to keep this posting alive with a bump , someone else maybe able to come up with a CSE ( aka array) formula that will do the trick :wink:

Assumptions:
dollars in column A
Start Date column B
Final Date column C

Public Sub MaxOwed()
Dim StartDate As Date
Dim FinalDate As Date
Dim OneDay As Date

StartDate = Application.WorksheetFunction.Min(Columns("B:B"))
FinalDate = Application.WorksheetFunction.Max(Columns("C:C"))

Do
    OneDay = DateAdd("d", lp, StartDate)
    lp = lp + 1
    CurrTot = 0
    
    For Rw = 1 To Cells(65536, 1).End(xlUp).Row
        With Cells(Rw, 1)
            If IsNumeric(.Value) And _
            IsDate(.Offset(0, 1).Value) And _
            IsDate(.Offset(0, 2).Value) Then
            
            If .Offset(0, 1).Value <= OneDay And _
                .Offset(0, 2).Value >= OneDay Then
                CurrTot = CurrTot + .Value
            End If
            End If
        End With
    Next Rw
    
    If CurrTot > CurrMax Then CurrMax = CurrTot

Loop Until OneDay > FinalDate Or lp > 20000


MsgBox CurrMax

End Sub

TO INSTALL CODE in module:
1.Open the Workbook you want code copied into
2.Press the Keys ALT and F11 at the same time (this open VBE window)
3.In VBE window goto tool bar and click "INSERT"
4.On Insert drop down menu click "Module"
5.A new module winow will appear on the rt side of VBE
6.Paste the code and close VBE by pressing ALT + F11

TO RUN MY CODE:
1.While the sheet to evaluate is active goto tool bar
2. ON tool bar goto "Tools...Macro....Macros"
3. A list of Macros should appear
4. To run the above Macro Double Click on it's name in the list

Note: This is a very basic way to initiate a macro... several other
methods include workbook event triggers, on sheet command
buttons/controls and shortcut keys .