Countif Question

[Ajay]

Hi
I need to count the number of entries in column I based on the date they
were entered - this appears in Column C

I only want the number of entries in Column I which contain the text
"Awaiting inspection" using the criteria from column C the date of entry if
it was before 31 Jan 2009

Any advice would be appreciated
Cheers
Ajay

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awaiting inspection"))

Mike

 

[Ajay]

Excellent just what I needed many thanks Mike H.

secondary question could I adapt the formula 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.

Cheers
ajay

 

[Mike H]

Hi,

Glad I could help and yes you can extend the formula like this

=SUMPRODUCT((C1:C36>DATE(2009,1,31))*(C1:C36<DATE(2009,2,28))*(I1:I36="Awaiting inspection"))

because it's now getting a bit long consider this

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

Which uses cell ref's for the dates and searchstring

Mike

 

[Ajay]

Exxcellent again Mike H
Many thanx

Hi,

Glad I could help and yes you can extend the formula like this

=SUMPRODUCT((C1:C36>DATE(2009,1,31))*(C1:C36<DATE(2009,2,28))*(I1:I36="Awaiting inspection"))

because it's now getting a bit long consider this

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

Which uses cell ref's for the dates and searchstring

Mike