Hi Ron!

Not sure how this is supposed to work, but........

A2 = 12/1
B2 = empty (not closed, still open)
C2 = 1000
D1 = 12/4

Formula return = 0

Biff

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:EC00101C-05BF-429C-9D91-3E61B59F0EBB@microsoft.com...
> Try this:
> Using your data in Cells A1:B5....
> D1: (any date you enter)
> E1: =SUMPRODUCT(+((A2:A5)<=D1)*(D1<=(B2:B5))*C2:C5)
>
> That will return the sum of all required amounts for items that are active
> as of the input date.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "tx12345" wrote:
>
>>
>> Hi
>>
>> I have an Excel brain twister for you. I am tryng to find a simple
>> spreadsheet formula to address the scenario found below. Any ideas will
>> be warmly received.
>>
>>
>> The scenario is this:
>>
>>
>> -|a..... |b.......|c.............|
>> 1 open| close| required
>> 2 12/1 | 12/5 | 1000
>> 3 12/2 | 12/2 | 5000
>> 4 12/3 | 12/4 | 2000
>> 5 12/4 | 12/4 | 4000
>>
>> In this case, I want to track what the maximum requirement is at any
>> given time. As long as the item is open, the requirement is in force.
>> Once it is closed, it is no longer necessary.
>>
>> At the start of 12/1 the maximum requirement was 1000
>> At the end of 12/1 the maximum requirement was 1000
>> At the start of 12/2 the maximum requirement was 6000
>> At end of 12/2 the maximum requirement was 1000
>> At the start of 12/3 the maximum requirement was 3000
>> At the end of 12/3 the maximum requirement was 3000
>> At the start of 12/4 the maximum requirement was 7000
>> At the end of 12/4 the maximum requirement was 1000
>>
>> Thus, during this period, the maximum needed was 7000. Is there a
>> simple formula to track this change?
>>
>> Many Thx
>>
>>
>> --
>> tx12345
>> ------------------------------------------------------------------------
>> tx12345's Profile:
>> http://www.excelforum.com/member.php...o&userid=24776
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=490867
>>
>>