SUMPRODUCT between two dates

Originally Posted by 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

Hi,

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

hth
---

Originally Posted by 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.

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)

Originally Posted by 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'.

Good, that works

Try the new date test (blue stuff)

--