SUMPRODUCT between two dates

[danviggers]

Hi all I'm looking to use the below forumla

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A12)*--('Monthly Release''s'!$M$3:$M$2500=B$11))

but change the green underlined value to be BETWEEN two dates.

Any ideas ???

Thanks

[Bryan Hessey]

Hi all I'm looking to use the below forumla

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A12)*--('Monthly Release''s'!$M$3:$M$2500=B$11))

but change the green underlined value to be BETWEEN two dates.

Any ideas ???

Thanks

Hi,

easiest would be to make the green >= and add another condition as <=

hth
---

[danviggers]

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$K12)*--('Monthly Release''s'!$K$3:$K$2500=2/10/2006>=5/10/2006))

OK so I've done this and the value comes out as 23, when it shoudl be 1.

There are 23 records that coincide with 'Monthly Release''s'!$G$3:$G2500=$K12, but there should be only 1 record that coincides with that AND has the date which has been shown above.

I don't think I'm writing the date right.

[Bryan Hessey]

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$K12)*--('Monthly Release''s'!$K$3:$K$2500=2/10/2006>=5/10/2006))

OK so I've done this and the value comes out as 23, when it shoudl be 1.

There are 23 records that coincide with 'Monthly Release''s'!$G$3:$G2500=$K12, but there should be only 1 record that coincides with that AND has the date which has been shown above.

I don't think I'm writing the date right.

Hi,

How about

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$K12)*--('Monthly Release''s'!$K$3:$K$2500=2/10/2006>=5/10/2006))

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$K12)*--('Monthly Release''s'!$K$3:$K$2500=2/10/2006)*--('Monthly Release''s'!$K$3:$K$2500>=5/10/2006)))

added, >= 2/10 ___ then <=5/10 ?

(untested)

[danviggers]

Thanks for the help.

Tried the formula =SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$K12)*--('Monthly Release''s'!$K$3:$K$2500=2/10/2006)*--('Monthly Release''s'!$K$3:$K$2500>=5/10/2006)) ... and it gives the number '0'.

[Bryan Hessey]

Thanks for the help.

Tried the formula =SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$K12)*--('Monthly Release''s'!$K$3:$K$2500=2/10/2006)*--('Monthly Release''s'!$K$3:$K$2500>=5/10/2006)) ... and it gives the number '0'.

Good, that works

Try the new date test (blue stuff)

--

[Carim]

Hi,

You should be careful about the worksheet name in your formula ...

'' appears in the middle of 'Monthly Release''s'!

My suggestion, if need be is to change the worksheet name to
Monthly Releases ... to avoid all possible problems ...

HTH
Carim

[danviggers]

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A31)*--('Monthly Release''s'!$K$3:$K$2500>=2/10/2006)*--('Monthly Release''s'!$K$3:$K$2500<=5/10/2006))

Still gives me a '0'.

Once again thanks for your help.

[Bryan Hessey]

Hi,

You should be careful about the worksheet name in your formula ...

'' appears in the middle of 'Monthly Release''s'!

My suggestion, if need be is to change the worksheet name to
Monthly Releases ... to avoid all possible problems ...

HTH
Carim

'' is as is required for names containing a ' (tick)

---

[Bryan Hessey]

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A31)*--('Monthly Release''s'!$K$3:$K$2500>=2/10/2006)*--('Monthly Release''s'!$K$3:$K$2500<=5/10/2006))

Still gives me a '0'.

Once again thanks for your help.

hmm, just to test, try the >=2/10 as = the date sought, and the same for the <=5/10

trying for a 1

or .zip and post a sample of your sheet for testing.

---

[danviggers]

Right I've got it now, thanks for your help.

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A31)*--('Monthly Release''s'!$K$3:$K$2500>DATE(2006,10,2))*--('Monthly Release''s'!$K$3:$K$2500<=DATE(2006,10,5)))

[Bryan Hessey]

Right I've got it now, thanks for your help.

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A31)*--('Monthly Release''s'!$K$3:$K$2500>DATE(2006,10,2))*--('Monthly Release''s'!$K$3:$K$2500<=DATE(2006,10,5)))

Hi,

good to see, I was just about to post a =SUMPRODUCT(--($G$3:$G5=$A3)*--($K$3:$K$5>=$H$1)*--($K$3:$K$5<=DATE(2006,11,11))) to show either put the date in a cell or 'date' it'


Thanks for the response.

---

[Carim]

Quite often, when dealing with dates in a sumproduct() formula, it is far better to have dates stored in their own separate cells ...

1. To allow for easy changes
2. and, more importantly, to avoid all the headache of date formatting

HTH
Carim

[Bryan Hessey]

Quite often, when dealing with dates in a sumproduct() formula, it is far better to have dates stored in their own separate cells ...

1. To allow for easy changes
2. and, more importantly, to avoid all the headache of date formatting

HTH
Carim

True, my preference is for a cell usually on row one or two, the latter if headers prevent row 1 being used.

---

[daddylonglegs]

Right I've got it now, thanks for your help.

=SUMPRODUCT(--('Monthly Release''s'!$G$3:$G2500=$A31)*--('Monthly Release''s'!$K$3:$K$2500>DATE(2006,10,2))*--('Monthly Release''s'!$K$3:$K$2500<=DATE(2006,10,5)))

Note: the suggested syntax here is a little confused. You never need *-- together, either use just the *, e.g.

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")) or use

=SUMPRODUCT(--(A1:A10="x"),--(B1:B10="y"))