| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if between two dates
Thanks guys, worked like a charm!!!!!!
"Peo Sjoblom" wrote: Doh! I don't know how I got that, I had 10 instead of 150 in the middle column. -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Toppers" wrote in message ... Peo, Middle column is 584. ... not sure how you got 444! "Peo Sjoblom" wrote: I guess I must have copied data from the post incorrectly. I copied this part 7/3 7/10 7/17 100 150 200 200 101 252 125 333 222 Total for each column is 425 444 674 gives a total of 1543 and 444 for the middle column and the total of the values under the dates is 1543? You and the OP get another 140 extra -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Toppers" wrote in message ... FYI =SUMPRODUCT((E7:G7=D4)*(E7:G7<=E4)*(E8:G10)) I get it to return 1683 on the posted data using the above and 584 if "=" removed.. So just adjust ranges to suit e.g =SUMPRODUCT((E7:R7=D4)*(E7:R7<=E4)*(E8:R100)) HTH "Peo Sjoblom" wrote: When you say didn't work what do you mean, computer exploded or what? It always help the person who tries to help if the OP explains what does not work, given your example if that is what it is based on I find it hard to get 1683 since the total of all values in your example is 1543. What part of the values in your example would you want to be included in the total if the between dates are 7/3/06 and 7/17/06 and by between what do you mean, including 7/3/06 and 7/17/06 or excluding those dates If those should be included use =SUMPRODUCT((E7:G7=D4)*(E7:G7<=E4)*(E8:G10)) which returns 1543 thus all values are included or =SUMPRODUCT((E7:G7D4)*(E7:G7<E4)*(E8:G10)) which returns 444 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Louisville Cardinals" wrote in message ... Thanks but that did not work. Spread sheet looks something like E F G 7 7/3 7/10 7/17 8 100 150 200 9 200 101 252 10 125 333 222 D4 would contain 7/3 and E4 contain 7/17. I would like for Excel to look at dates in D and E 4 and sum numbers under the columns that are between the dates in D and E. Total should be something like 1683. Once again Thanks for your help. "Toppers" wrote: ... sorry should be .... =SUMPRODUCT((E7:R7=D4)*(E7:R7<=E4)*(E8:R8)) "Toppers" wrote: Try: =SUMPRODUCT((E7:R7=E4)*(E7:R7<=E5)*(E8:R8)) HTH "Louisville Cardinals" wrote: I have a spread sheet that in row e7 through r7 is a date that represnets the week of. Example exapmle e7=7/3, f7=7/10, g7=7/17. E8 through r25 contains numbers which are number of pieces forecasted to be ordered. What I would like to be able to do is type a date like 7/3 in d4 and 7/31 in e4 and have excel look at the dates in row 7 and if greater than or equal lower date and less than or equal to second date sum numbers in the corresponding columns. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates and Intervals | Excel Worksheet Functions | |||
| calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
| formula to add dates. | Excel Worksheet Functions | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Hybrid Mode
