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

Hi,

Try this

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

Mike

"Ajay" wrote:

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

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" wrote:

Hi,

Try this

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

Mike

"Ajay" wrote:

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

Hi,

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

=SUMPRODUCT((C1:C36DATE(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:C36A1)*(C1:C36<A2)*(I1:I36=A3))

Which uses cell ref's for the dates and searchstring

Mike



"Ajay" wrote:

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" wrote:

Hi,

Try this

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

Mike

"Ajay" wrote:

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

Exxcellent again Mike H
Many thanx

"Mike H" wrote:

Hi,

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

=SUMPRODUCT((C1:C36DATE(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:C36A1)*(C1:C36<A2)*(I1:I36=A3))

Which uses cell ref's for the dates and searchstring

Mike



"Ajay" wrote:

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" wrote:

Hi,

Try this

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

Mike

"Ajay" wrote:

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