Follow up Question to Countif

[Ajay]

Hi All

Secondary question could I adapt the formula
=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awaiting inspection"))

to give a count using the criteria of between two dates.

Eg Count Awaiting Inspection entries if date in column B is between 1st feb
2009 and 15th Feb 2009.

Many thanx
Ajay

 

[T. Valko]

to give a count using the criteria of between two dates.

Usually, when someone says "between dates" what they really mean is all
dates between, *including* the boundary dates.

Use cells to hold the boundary dates and your other criteria:

A1 = lower boundary date = 2/1/2009
A2 = upper boundary date = 2/15/2009
A3 = Awaiting inspection

Then:

=SUMPRODUCT(--(C1:C36>=A1),--(C1:C36<=B1),--(I1:I36=A3))

 

[T. Valko]

Ooops!

Typo:

=SUMPRODUCT(--(C1:C36>=A1),--(C1:C36<=B1),--(I1:I36=A3))

Should be:

=SUMPRODUCT(--(C1:C36>=A1),--(C1:C36<=A2),--(I1:I36=A3))

 

[Ajay]

Excellent many thanks just what i needed

Ooops!

Typo:


Should be:

=SUMPRODUCT(--(C1:C36>=A1),--(C1:C36<=A2),--(I1:I36=A3))

 

[T. Valko]

You're welcome. Thanks for the feedback!