Sumproduct by date

[heater]

I need a formula that looks at three different cells with dates in two of the
cells and a dollar amount in the other that will give a cumulative total over
time if column d is before column F. For example: D10=3/27/2006 and
F10=5/3/2006 and E10=609,214. Since D10 is before F10, cell P10 would equal
609,214. Now, if D11=4/14/2006 and F11=4/12/2006 and E11=5,055,000 then cell
P11 would still =609,214, because D11 is after f11. Column D10 and F10 have
dates down to row 17 and column E has dollar figues down to row 17. I have
tried the following
=SUMPRODUCT(--($D$10:$D$17<=F10),$E$10:$E$17)

[Dave Peterson]

How about:

=SUMPRODUCT(--($D$10:$D$17<=$F$10:$F$17),$E$10:$E$17)



heater wrote:
>
> I need a formula that looks at three different cells with dates in two of the
> cells and a dollar amount in the other that will give a cumulative total over
> time if column d is before column F. For example: D10=3/27/2006 and
> F10=5/3/2006 and E10=609,214. Since D10 is before F10, cell P10 would equal
> 609,214. Now, if D11=4/14/2006 and F11=4/12/2006 and E11=5,055,000 then cell
> P11 would still =609,214, because D11 is after f11. Column D10 and F10 have
> dates down to row 17 and column E has dollar figues down to row 17. I have
> tried the following
> =SUMPRODUCT(--($D$10:$D$17<=F10),$E$10:$E$17)

--

Dave Peterson

[heater]

That formula does give me the cumulative total; however, I need a running
total of how much money is out at any given specified time. In other words
by each row, The inputs are as follows:
Column D Column E Column F
Row 10 Mon, Mar 27, 2006 609,214 Wed, May 03, 2006
Row 11 Wed, Mar 29, 2006 1,279,580 Wed, May 24, 2006
Row 12 Thu, Mar 30, 2006 880,376 Wed, May 24, 2006
Row 13 Mon, Apr 10, 2006 618,935 Wed, Apr 05, 2006
Row 14 Fri, Apr 14, 2006 5,055,000 Wed, Apr 12, 2006
Row 15 Fri, Apr 14, 2006 2,619,321 Wed, May 03, 2006
Row 16 Fri, Apr 14, 2006 2,298,985 Wed, May 03, 2006
Row 17 Tue, Apr 25, 2006 8,262,629 Wed, May 03, 2006

So, the results should be as follows:

P10 = 609,214
P11 = 1,888,794
P12 = 2,769,170
P13 = 2,769,170
P14 = 2,769,170
P15 = 5,388,491
P16 = 7,687,476
P17 = 15,950,105


"Dave Peterson" wrote:

> How about:
>
> =SUMPRODUCT(--($D$10:$D$17<=$F$10:$F$17),$E$10:$E$17)
>
>
>
> heater wrote:
> >
> > I need a formula that looks at three different cells with dates in two of the
> > cells and a dollar amount in the other that will give a cumulative total over
> > time if column d is before column F. For example: D10=3/27/2006 and
> > F10=5/3/2006 and E10=609,214. Since D10 is before F10, cell P10 would equal
> > 609,214. Now, if D11=4/14/2006 and F11=4/12/2006 and E11=5,055,000 then cell
> > P11 would still =609,214, because D11 is after f11. Column D10 and F10 have
> > dates down to row 17 and column E has dollar figues down to row 17. I have
> > tried the following
> > =SUMPRODUCT(--($D$10:$D$17<=F10),$E$10:$E$17)
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Put this in G10 and drag down:

=SUMPRODUCT(--($D$10:D10<=$F$10:F10),$E$10:E10)

Notice that the $'s are gone in the second part of each range. This means that
the formula will adjust when you copy it down.

heater wrote:
>
> That formula does give me the cumulative total; however, I need a running
> total of how much money is out at any given specified time. In other words
> by each row, The inputs are as follows:
> Column D Column E Column F
> Row 10 Mon, Mar 27, 2006 609,214 Wed, May 03, 2006
> Row 11 Wed, Mar 29, 2006 1,279,580 Wed, May 24, 2006
> Row 12 Thu, Mar 30, 2006 880,376 Wed, May 24, 2006
> Row 13 Mon, Apr 10, 2006 618,935 Wed, Apr 05, 2006
> Row 14 Fri, Apr 14, 2006 5,055,000 Wed, Apr 12, 2006
> Row 15 Fri, Apr 14, 2006 2,619,321 Wed, May 03, 2006
> Row 16 Fri, Apr 14, 2006 2,298,985 Wed, May 03, 2006
> Row 17 Tue, Apr 25, 2006 8,262,629 Wed, May 03, 2006
>
> So, the results should be as follows:
>
> P10 = 609,214
> P11 = 1,888,794
> P12 = 2,769,170
> P13 = 2,769,170
> P14 = 2,769,170
> P15 = 5,388,491
> P16 = 7,687,476
> P17 = 15,950,105
>
> "Dave Peterson" wrote:
>
> > How about:
> >
> > =SUMPRODUCT(--($D$10:$D$17<=$F$10:$F$17),$E$10:$E$17)
> >
> >
> >
> > heater wrote:
> > >
> > > I need a formula that looks at three different cells with dates in two of the
> > > cells and a dollar amount in the other that will give a cumulative total over
> > > time if column d is before column F. For example: D10=3/27/2006 and
> > > F10=5/3/2006 and E10=609,214. Since D10 is before F10, cell P10 would equal
> > > 609,214. Now, if D11=4/14/2006 and F11=4/12/2006 and E11=5,055,000 then cell
> > > P11 would still =609,214, because D11 is after f11. Column D10 and F10 have
> > > dates down to row 17 and column E has dollar figues down to row 17. I have
> > > tried the following
> > > =SUMPRODUCT(--($D$10:$D$17<=F10),$E$10:$E$17)
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[heater]

Outstanding - Thanks!

"Dave Peterson" wrote:

> Put this in G10 and drag down:
>
> =SUMPRODUCT(--($D$10:D10<=$F$10:F10),$E$10:E10)
>
> Notice that the $'s are gone in the second part of each range. This means that
> the formula will adjust when you copy it down.
>
> heater wrote:
> >
> > That formula does give me the cumulative total; however, I need a running
> > total of how much money is out at any given specified time. In other words
> > by each row, The inputs are as follows:
> > Column D Column E Column F
> > Row 10 Mon, Mar 27, 2006 609,214 Wed, May 03, 2006
> > Row 11 Wed, Mar 29, 2006 1,279,580 Wed, May 24, 2006
> > Row 12 Thu, Mar 30, 2006 880,376 Wed, May 24, 2006
> > Row 13 Mon, Apr 10, 2006 618,935 Wed, Apr 05, 2006
> > Row 14 Fri, Apr 14, 2006 5,055,000 Wed, Apr 12, 2006
> > Row 15 Fri, Apr 14, 2006 2,619,321 Wed, May 03, 2006
> > Row 16 Fri, Apr 14, 2006 2,298,985 Wed, May 03, 2006
> > Row 17 Tue, Apr 25, 2006 8,262,629 Wed, May 03, 2006
> >
> > So, the results should be as follows:
> >
> > P10 = 609,214
> > P11 = 1,888,794
> > P12 = 2,769,170
> > P13 = 2,769,170
> > P14 = 2,769,170
> > P15 = 5,388,491
> > P16 = 7,687,476
> > P17 = 15,950,105
> >
> > "Dave Peterson" wrote:
> >
> > > How about:
> > >
> > > =SUMPRODUCT(--($D$10:$D$17<=$F$10:$F$17),$E$10:$E$17)
> > >
> > >
> > >
> > > heater wrote:
> > > >
> > > > I need a formula that looks at three different cells with dates in two of the
> > > > cells and a dollar amount in the other that will give a cumulative total over
> > > > time if column d is before column F. For example: D10=3/27/2006 and
> > > > F10=5/3/2006 and E10=609,214. Since D10 is before F10, cell P10 would equal
> > > > 609,214. Now, if D11=4/14/2006 and F11=4/12/2006 and E11=5,055,000 then cell
> > > > P11 would still =609,214, because D11 is after f11. Column D10 and F10 have
> > > > dates down to row 17 and column E has dollar figues down to row 17. I have
> > > > tried the following
> > > > =SUMPRODUCT(--($D$10:$D$17<=F10),$E$10:$E$17)
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>