Counting Occurrences in two different columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dustinaspray@gmail.com - 12 Jan 2007 15:38 GMT

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

Reply to this Message

Dave Peterson - 12 Jan 2007 15:53 GMT

=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.

> 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
[quoted text clipped - 8 lines]
> 5 Atlanta 1
> 6 Atlanta 2

Signature

Dave Peterson

Reply to this Message

Rate this thread: