sumproduct between date range

[Looping through]

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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

 

[Don Guillett]

how about this copied down
=sumproduct(year(daterng)=2007)*(month(daterng)=row(a1))*valuerng)

 

[Looping through]

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]

Did you try it first? Just copy down 12 for 12 months. The row(a1) changes
as you copy DOWN

 

[Looping through]

I did but I got error messages. I tried to change the formula to what I
thought you neant, but again errors.

Peter

 

[David Biddulph]

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.]

 

[Looping through]

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(Sheet1!$F$3:$F$300)=row(a1))*$K$3:$K$300).

I got "The formula you typed in contains errors"

Does this help?

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

I did but I got error messages. I tried to change the formula to what I
thought you neant, but again errors.

 

[Don Guillett]

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 (e-mail address removed)"Looping through" <[email protected]> wrote inmessage news:[email protected]...> 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(Sheet1!$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" <[email protected]> wrote in>> message news:[email protected]...>> >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" <[email protected]> wrote in>> >> message news:[email protected]...>> >> > 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)=row(a1))*valuerng)>> >> >>>> >> >> -->> >> >> Don Guillett>> >> >> Microsoft MVP Excel>> >> >> SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping through" <[email protected]> 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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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>> >> >>>> >> >>>> >>>> >>>>>>>>

 

[David Biddulph]

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)=row(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" <[email protected]> wrote inmessage news:[email protected]...> 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(Sheet1!$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" <[email protected]> wrote in>> message news:[email protected]...>> >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" <[email protected]> wrote in>> >> message news:[email protected]...>> >> > 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)=row(a1))*valuerng)>> >> >>>> >> >> -->> >> >> Don Guillett>> >> >> Microsoft MVP Excel>> >> >> SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping through" <[email protected]> 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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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]

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 (e-mail address removed)"Don Guillett" <[email protected]> wrote in messagenews:[email protected]...> try this>>sumproduct((year(Sheet1!$F$3:$F$300)=2007)*(Sheet1!$F$3:$F$300)=row(a1))*$K$3:$K$300)--Don GuillettMicrosoft MVP (e-mail address removed)"Looping through"<[email protected]> wrote inmessagenews:[email protected]...> I'll try hardernext time...>> The first error I got was "The formula you typed in containserrors"> After changing>sumproduct(year(daterng)=2007)*(month(daterng)=row(a1))*valuerng)to>sumproduct(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" <[email protected]>wrote in>> messagenews:[email protected]...>> >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 through"<[email protected]> wrote in>> >> messagenews:[email protected]...>> >> > 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)=row(a1))*valuerng)>>>> >>>> >> >> -->> >> >> Don Guillett>> >> >> Microsoft MVP Excel>> >> >>SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping through"<[email protected]> wrotein>> >> >> message>> >> >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))*(Sheet1!$K$3:$K$300>0)*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>> >> >>>> >>>>>> >>>> >>>>>>>>

 

[Looping through]

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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$300>DATE(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))*(Sheet1!$K$3:$K$300>0)*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

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)=row(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" <[email protected]> wrote inmessage news:[email protected]...> 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(Sheet1!$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

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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$300>DATE(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))*(Sheet1!$K$3:$K$300>0)*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

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)=row(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" <[email protected]>
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)
to>sumproduct(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">> Does this help?>>
:>>> 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"
>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>>>> > 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>> >>SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping

I am using the following the formula to look at all the orders Ihave>> >>

how can thisbe>> >> >> > done?>> >> >> >>> >> >>well. But Ido>> >> >> > not>> >> >> > want to include anything before
1/1/2007.>> >> >> >>> >> >> > Thanks>> >> >> > Peter>> >> >>>> >> >>>>

 

[Don Guillett]

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
(e-mail address removed)

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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$300>DATE(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))*(Sheet1!$K$3:$K$300>0)*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

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)=row(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" <[email protected]>
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)
to>sumproduct(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">> Does this help?>>
:>>> 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"
>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>>>> > 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>> >>SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping

I am using the following the formula to look at all the orders Ihave>> >>

how can thisbe>> >> >> > done?>> >> >> >>> >> >>well. But Ido>> >> >> > not>> >> >> > want to include anything before
1/1/2007.>> >> >> >>> >> >> > Thanks>> >> >> > Peter>> >> >>>> >> >>>>

 

[Don Guillett]

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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$300>DATE(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))*(Sheet1!$K$3:$K$300>0)*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

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)=row(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" <[email protected]>
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)
to>sumproduct(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">> Does this help?>>
:>>> 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"
>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"

>> > 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)=row(a1))*valuerng)>> >>

-->> >> >> Don Guillett>> >> >> Microsoft MVP Excel>> >> >>

SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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]

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
(e-mail address removed)

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
(e-mail address removed)

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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$300>DATE(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))*(Sheet1!$K$3:$K$300>0)*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

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)=row(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" <[email protected]>
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)
to>sumproduct(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">> Does this help?>>
:>>> 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"
>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"

>> > 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)=row(a1))*valuerng)>> >>

-->> >> >> Don Guillett>> >> >> Microsoft MVP Excel>> >> >>

SalesAid Software>> >> >> (e-mail address removed)>> >> >> "Looping

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))*Sheet1!$K$3:$K$300)/SUMPRODUCT((Sheet1!$F$3:$F$300<DATE(2007,1,1))*(Sheet1!$K$3:$K$300>0)*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>> >> >>>> >> >>>>