| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct between date range
I am using the following the formula to look at all the orders I have from
2006. I now want to look at each month individually. how can this be done? =SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*Sh eet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sh eet1!$K$3:$K$3000)*Sheet1!$D$3:$D$300) I will also need to do this for each month in 2007 as well. But I do not want to include anything before 1/1/2007. Thanks Peter |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct between date range
how about this copied down
=sumproduct(year(daterng)=2007)*(month(daterng)=ro w(a1))*valuerng) -- Don Guillett Microsoft MVP Excel SalesAid Software "Looping through" wrote in message ... I am using the following the formula to look at all the orders I have from 2006. I now want to look at each month individually. how can this be done? =SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*Sh eet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sh eet1!$K$3:$K$3000)*Sheet1!$D$3:$D$300) I will also need to do this for each month in 2007 as well. But I do not want to include anything before 1/1/2007. Thanks Peter |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct between date range
Cna you please explain you formula. I don't follow? Do I copy down to
"December" or down to the end of my data range? Thanks "Don Guillett" wrote: how about this copied down =sumproduct(year(daterng)=2007)*(month(daterng)=ro w(a1))*valuerng) -- Don Guillett Microsoft MVP Excel SalesAid Software "Looping through" wrote in message ... I am using the following the formula to look at all the orders I have from 2006. I now want to look at each month individually. how can this be done? =SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*Sh eet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sh eet1!$K$3:$K$3000)*Sheet1!$D$3:$D$300) I will also need to do this for each month in 2007 as well. But I do not want to include anything before 1/1/2007. Thanks Peter |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct between date range
I did but I got error messages. I tried to change the formula to what I
thought you neant, but again errors. Peter "Don Guillett" wrote: Did you try it first? Just copy down 12 for 12 months. The row(a1) changes as you copy DOWN -- Don Guillett Microsoft MVP Excel SalesAid Software "Looping through" wrote in message ... Cna you please explain you formula. I don't follow? Do I copy down to "December" or down to the end of my data range? Thanks "Don Guillett" wrote: how about this copied down =sumproduct(year(daterng)=2007)*(month(daterng)=ro w(a1))*valuerng) -- Don Guillett Microsoft MVP Excel SalesAid Software "Looping through" wrote in message ... I am using the following the formula to look at all the orders I have from 2006. I now want to look at each month individually. how can this be done? =SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*Sh eet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sh eet1!$K$3:$K$3000)*Sheet1!$D$3:$D$300) I will also need to do this for each month in 2007 as well. But I do not want to include anything before 1/1/2007. Thanks Peter |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct between date range
And you want us to guess *what* the error messages were, and you also want
us to guess what you thought Don meant and what you changed the formula to? I assume that you are happy to guess at the answer if you want us to guess at the question? [And this reply isn't just criticising you, but it is to remind all those asking questions that the experts (and the rest of us!) don't have crystal balls to allow us to see what you've got on your screen.] -- David Biddulph "Looping through" wrote in message ... I did but I got error messages. I tried to change the formula to what I thought you neant, but again errors. "Don Guillett" wrote: Did you try it first? Just copy down 12 for 12 months. The row(a1) changes as you copy DOWN "Looping through" wrote in message ... Cna you please explain you formula. I don't follow? Do I copy down to "December" or down to the end of my data range? Thanks "Don Guillett" wrote: how about this copied down =sumproduct(year(daterng)=2007)*(month(daterng)=ro w(a1))*valuerng) -- Don Guillett Microsoft MVP Excel SalesAid Software "Looping through" wrote in message ... I am using the following the formula to look at all the orders I have from 2006. I now want to look at each month individually. how can this be done? =SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*Sh eet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sh eet1!$K$3:$K$3000)*Sheet1!$D$3:$D$300) I will also need to do this for each month in 2007 as well. But I do not want to include anything before 1/1/2007. Thanks Peter |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a date range be used in SUMPRODUCT | Excel Worksheet Functions | |||
| SUMPRODUCT between date range | New Users to Excel | |||
| Sumproduct with date range | Excel Worksheet Functions | |||
| Sumproduct based on Date range | Excel Worksheet Functions | |||
| SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
Hybrid Mode
