corrected text:
Using your data in A1:C5

(not A1:B5)


***********
Regards,
Ron


"Ron Coderre" wrote:

> 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
> >
> >