# Finding the Maximum Amount Outstanding within a Date Range

#### Met123dm

##### New Member
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?

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Code:
``=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.

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!

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

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

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

Code:
``````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 .

Replies
7
Views
148
Replies
5
Views
182
Replies
4
Views
233
Replies
2
Views
357
Replies
5
Views
143

Threads
1,196,312
Messages
6,014,594
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back