COUNTIF problem
 

Hi All

I have 2 columns: A has dates (appointment dates), B has integer numbers
(current waiting times in weeks).
I basically want to do:
COUNTIF A1:A20 30/06/2007 AND B1:B20 6
In other words, of those with appointments after 30 June 2007, how many have
already been waiting more than 6 weeks
?? Possible with one statement of the form:
=COUNTIF(AND(A1:A20,"30/06/2007",B1:B20,"6"))

Any help appreciated.

Phil

COUNTIF problem
 

Hi Phil

=SUMPRODUCT((A1:A20--"30/06/2007")*(B1:B206))

--
Regards

Roger Govier


"Phil C" wrote in message
...
Hi All

I have 2 columns: A has dates (appointment dates), B has integer
numbers (current waiting times in weeks).
I basically want to do:
COUNTIF A1:A20 30/06/2007 AND B1:B20 6
In other words, of those with appointments after 30 June 2007, how
many have already been waiting more than 6 weeks
?? Possible with one statement of the form:
=COUNTIF(AND(A1:A20,"30/06/2007",B1:B20,"6"))

Any help appreciated.

Phil

COUNTIF problem
 

=SUMPRODUCT(--(A1:A100DATE(2007,30,06)),--(B1:B1006))

[the "--" converts TRUE/False to 1/0]

"Phil C" wrote:

Hi All

I have 2 columns: A has dates (appointment dates), B has integer numbers
(current waiting times in weeks).
I basically want to do:
COUNTIF A1:A20 30/06/2007 AND B1:B20 6
In other words, of those with appointments after 30 June 2007, how many have
already been waiting more than 6 weeks
?? Possible with one statement of the form:
=COUNTIF(AND(A1:A20,"30/06/2007",B1:B20,"6"))

Any help appreciated.

Phil

COUNTIF problem
 

Hi Roger

That works! Many thanks.

Phil


"Roger Govier" wrote in message
...
Hi Phil

=SUMPRODUCT((A1:A20--"30/06/2007")*(B1:B206))

--
Regards

Roger Govier


"Phil C" wrote in message
...
Hi All

I have 2 columns: A has dates (appointment dates), B has integer numbers
(current waiting times in weeks).
I basically want to do:
COUNTIF A1:A20 30/06/2007 AND B1:B20 6
In other words, of those with appointments after 30 June 2007, how many
have already been waiting more than 6 weeks
?? Possible with one statement of the form:
=COUNTIF(AND(A1:A20,"30/06/2007",B1:B20,"6"))

Any help appreciated.

Phil

COUNTIF problem
 

Try this and put a date into cell C1 to compare
=SUMPRODUCT(--(A1:A100(C1)),--(B1:B1006))

"Phil C" wrote:

Hi All

I have 2 columns: A has dates (appointment dates), B has integer numbers
(current waiting times in weeks).
I basically want to do:
COUNTIF A1:A20 30/06/2007 AND B1:B20 6
In other words, of those with appointments after 30 June 2007, how many have
already been waiting more than 6 weeks
?? Possible with one statement of the form:
=COUNTIF(AND(A1:A20,"30/06/2007",B1:B20,"6"))

Any help appreciated.

Phil