formula for multiple criteria inc. date range

  • Thread starter Thread starter J.Scargill
  • Start date Start date
J

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?
 
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.
 
Try

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