| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct by date
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct by date
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct by date
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct by date
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct by date
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| average value from a table | Excel Discussion (Misc queries) | |||
| How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
| Sumproduct with Date column | Excel Worksheet Functions | |||
| Using sumproduct to count number by date | Excel Worksheet Functions | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Hybrid Mode
