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

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

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

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

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

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

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) to
sumproduct(year(Sheet1!$F$3:$F$300)=2007)*(1(Sheet 1!$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 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

try this

sumproduct((year(Sheet1!$F$3:$F$300)=2007)*(Sheet1 !$F$3:$F$300)=row(a1))*$K$3:$K$300)--Don GuillettMicrosoft MVP ExcelSalesAid "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

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

oops
sumproduct((year(Sheet1!$F$3:$F$300)=2007)*(Sheet1 !$F$3:$F$300)=row(a1))*$K$3:$K$300)--
for month
sumproduct((year(Sheet1!$F$3:$F$300)=2007)*(month( Sheet1!$F$3:$F$300)=row(a1))*$K$3:$K$300)--Don GuillettMicrosoft MVP ExcelSalesAid "Don Guillett" wrote in . .. try thissumproduct((year(Sheet1!$F$3:$F$300)=2007)*( Sheet1!$F$3:$F$300)=row(a1))*$K$3:$K$300)--Don GuillettMicrosoft MVP "Looping wrote ... I'll try hardernext time... The first error I got was "The formula you typed in containserrors" After changingsumproduct(year(daterng)=2007)*(month(dat erng)=row(a1))*valuerng)tosumproduct(year(Sheet1! $F$3:$F$300)=2007)*(1(Sheet1!$F$3:$F$300)=row(a1)) *$K$3:$K$300). I got "The formula you typed in contains errors" Doesthis help? "David Biddulph" wrote: And you want us to guess *what* theerror messages were, and you alsowant us to guess what you thought Donmeant and what you changed the formulato? I assume that you are happy toguess at the answer if you want us toguess at the question? [And thisreply isn't just criticising you, but it is to remind all those askingquestions that the experts (and the rest of us!) don't havecrystal ballsto allow us to see what you've got on your screen.] -- DavidBiddulph "Looping through" wrote in ... I did but Igot error messages. I tried to change the formula to what I thought youneant, but again errors. "Don Guillett" wrote: Did you try itfirst? Just copy down 12 for 12 months. The row(a1) changes as youcopy DOWN "Looping wrote in ... Cna youplease 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 wrotein ... I amusing the following the formula to look at all the orders Ihave from 2006. I now want to look at each month individually. how canthisbe done? =SUMPRODUCT((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) I will also need to do this for each month in2007 as well. But Ido not want to include anythingbefore 1/1/2007. Thanks Peter

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Looping through" wrote in
message ...
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

In the interest of getting this done, send me a workbook with a clear
explanation along with a couple of correct answers. Send to the address
below, not the list

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Looping through" wrote in
message ...
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

Send a workbook to me at the address below with some examples of correct
outcome

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Looping through" wrote in
message ...
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

For the archives

=IF(SUMPRODUCT(--(YEAR(colF)=2007)*--(MONTH(colF)=ROW(A1)),colD)=0,0,SUMPRODUCT(--(YEAR(colF)=2007)*--(MONTH(colF)=ROW(A1)),colK)/SUMPRODUCT(--(YEAR(colF)=2007),--(MONTH(colF)=ROW(A1)),colD))

=IF(SUMPRODUCT(--(YEAR(colF)=2007)*--(MONTH(colF)=ROW(A1)),colm)=0,0,SUMPRODUCT(--(YEAR(colF)=2007)*--(MONTH(colF)=ROW(A1)),colm)/SUMPRODUCT(--(YEAR(colF)=2007)*--(MONTH(colF)=ROW(A1)*--(colm<0)),colD))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
In the interest of getting this done, send me a workbook with a clear
explanation along with a couple of correct answers. Send to the address
below, not the list

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Looping through" wrote in
message ...
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