formula for multiple criteria inc. date range

[J.Scargill]

Hello,
I am new to this site and need help with a problem I am having on Excel.
I am trying to count the number of cells in column Q that have a "Y" in it,
provided that the date in column A is between 27/02/10 and 06/03/10. Does
this make sense??
I have tried;
=SUMPRODUCT((A14:A1000<="06/03/10")*(A14:A1000>="27/02/10")*(Q14:Q1000="Y"))
but this just returns a 0.
Can anybody help please?

 

[Mike H]

Hi,

One way

=SUMPRODUCT((A14:A1000<=DATE(2010,3,6))*(A14:A1000>=DATE(2010,2,27))*(Q14:Q1000="Y"))

But I suggest you keep the dates in a cell and refer to the cells

=SUMPRODUCT((A14:A1000<=A1)*(A14:A1000>=A2)*(Q14:Q1000="Y"))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Bob Phillips]

Try

=SUMPRODUCT(--(A14:A1000<=--"2010-03-06"),--(A14:A1000>=--"2010-02-27"),--(Q14:Q1000="Y"))

 

[J.Scargill]

Mike, you are a legend! That works perfectly. Many, many, many thanks.

 

[J.Scargill]

Thanks Bob, works great!

Try

=SUMPRODUCT(--(A14:A1000<=--"2010-03-06"),--(A14:A1000>=--"2010-02-27"),--(Q14:Q1000="Y"))


--

HTH

Bob




.