Counting Occurrences in two different columns

[dustinaspray]

I have two columns of data and want to count the number of occurences
when one column has something and they other something else. For
example I want to count the total number of occurences that have
Atlanta and 2. The answer should be 3. How do you do this?


A B
1 Atlanta 2
2 Atlanta 3
3 Chicago 2
4 Atlanta 2
5 Atlanta 1
6 Atlanta 2

 

[Dave Peterson]

=sumproduct(--(a1:a10="Atlanta"),--(b1:b10=2))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===
And if you're using xl2007, take a look at:

=countifs()
in excel's help.