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

[Guest]

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?

 

[JulieD]

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