Thread: want to count in two columns; countif (colA=x AND colB=y)?

I'm counting the number of occurences in a column:
COUNTIF (a1:a20,"Ontario")
the answer is 8 (8 of the 20 entries in the column say "Ontario")

But I want to be able to count the number of occurences in two columns:
COUNTIF (a1:a20,"Ontario")... AND(b1:b20,"Ottawa")
The answer should be 5 (5 of the 8 "ontario" rows include Ottawa in column b)

I've tried several configurations to count Ottawa, Ontario occurences; and
there is a problem with the function. Any ideas?

Hi Heather

One option is to use the SUMPRODUCT function

=SUMPRODUCT(--(A1:A20="Ontario"),--(B1:B20="Ottawa"))

for an explaination of sumproduct check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
JulieD

"Heather Murch" <Heather Murch@discussions.microsoft.com> wrote in message
news:80B1386A-CDD0-4844-BE7A-CC366CEAA5B0@microsoft.com...
> I'm counting the number of occurences in a column:
> COUNTIF (a1:a20,"Ontario")
> the answer is 8 (8 of the 20 entries in the column say "Ontario")
>
> But I want to be able to count the number of occurences in two columns:
> COUNTIF (a1:a20,"Ontario")... AND(b1:b20,"Ottawa")
> The answer should be 5 (5 of the 8 "ontario" rows include Ottawa in column
> b)
>
> I've tried several configurations to count Ottawa, Ontario occurences; and
> there is a problem with the function. Any ideas?