Follow up Question to Countif
 

Hi All

Secondary question could I adapt the formula
=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting 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

Follow up Question to Countif
 

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))


--
Biff
Microsoft Excel MVP


"Ajay" wrote in message
...
Hi All

Secondary question could I adapt the formula
=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting 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

Follow up Question to Countif
 

Ooops!

Typo:

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

Should be:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))


--
Biff
Microsoft Excel MVP


"Ajay" wrote in message
...
Hi All

Secondary question could I adapt the formula
=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting 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

Follow up Question to Countif
 

Excellent many thanks just what i needed

"T. Valko" wrote:

Ooops!

Typo:

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

Should be:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))


--
Biff
Microsoft Excel MVP


"Ajay" wrote in message
...
Hi All

Secondary question could I adapt the formula
=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting 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

Follow up Question to Countif
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ajay" wrote in message
...
Excellent many thanks just what i needed

"T. Valko" wrote:

Ooops!

Typo:

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

Should be:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))


--
Biff
Microsoft Excel MVP


"Ajay" wrote in message
...
Hi All

Secondary question could I adapt the formula
=SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting 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