ExcelBanter - sumproduct between date range

David/Don - I really do apprechiate your help with this formula. You guys
have probably forgotten more about excel than I will ever know.

I have tried all the fomulas included in the last couple of replys and
everything retuns 0.

Again this is my original formula that finds all the jobs for 2006 and
returns a percentage of profits made within the year.
=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).

Now I just want to focus on Jan, Feb etc... My original thought was this
could be done with a IF/AND type statement within the SUMPRODUCT formula.
Similar to

=SUMPRODUCT(if(and(Sheet1!$F$3:$F$300DATE(2007,1, 1),Sheet1!$F$3:$F$300<DATE(2007,2,1)),Sheet1!$F$3: $F$300<DATE(2007,1,1))*Sheet1!$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).....
if range is greater than jan 1 07 and less than feb 1,07 do SUMPRODUCT
formula.

I hope this clarifies what I am trying to do, but maybe this is just not
possible.

Thanks for being patient with me.
Novice Peter

"David Biddulph" wrote:

Well, there are a few problems.
1 In the first formula, did you have named ranges daterng and valuerng?
2 It's always a wise idea to count parentheses, and make sure you've got
them paired appropriately. I guess that Don probably intended something
like:
=sumproduct((year(daterng)=2007)*(month(daterng)=r ow(a1))*valuerng)
3 In your revised formula you've got *(1(Sheet1!$F$3:$F$300)= ... How do
you want to combine the 1 with the bracketted expression afterwards? Add?
Multiply? My guess is that you don't really want a 1 there and that you
want to use the MONTH function as Don suggested.
If those are the ranges you are using, try
=SUMPRODUCT((YEAR(Sheet1!$F$3:$F$300)=2007)*(MONTH (Sheet1!$F$3:$F$300)=ROW(A1))*$K$3:$K$300)for January, and copy down for the remaining 11 months.--David Biddulph"Looping through" wrote inmessage ... I'll try harder next time... The first error I got was "The formula you typed in contains errors" After changing sumproduct(year(daterng)=2007)*(month(daterng)=row (a1))*valuerng) tosumproduct(year(Sheet1!$F$3:$F$300)=2007)*(1(Sh eet1!$F$3:$F$300)=row(a1))*$K$3:$K$300). I got "The formula you typed in contains errors" Does this help? "David Biddulph" wrote: And you want us to guess *what* the error messages were, and you alsowant us to guess what you thought Don meant and what you changed the formulato? I assume that you are happy to guess at the answer if you want us toguess 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 havecrystal 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 downto "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" wrotein message ... I am using the following the formula to look at all the orders Ihave from 2006. I now want to look at each month individually. how can thisbe done? =SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*S heet1!$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 Ido not want to include anything before 1/1/2007. Thanks Peter