Comparing two excel columns to get a count

[excel_dummie]

Hello. I have two columns, one with the name of cities (Column A) and the other with the numbers 1 and 2 (Column B, representing gender). I want to run a formula that will look at the name of a city in column A and count the number of times number 1 occurs for that city. Below is a column example:

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">City</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Gender</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Boston</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Boston</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Boston</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Hello. I have two columns, one with the name of cities (Column A) and the other with the numbers 1 and 2 (Column B, representing gender). I want to run a formula that will look at the name of a city in column A and count the number of times number 1 occurs for that city. Below is a column example:

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">City</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">Gender</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Boston</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Miami</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Boston</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Boston</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>

On Excel 2007 and later...

=COUNTIFS($A$2:$A$8,"Boston",$B$2:$B$7,1)

You can have criteria like Boston and 1 in cells of their own:

E2: Boston
E3: Miami
Etc.

F1: 1
F2: 2

and invoke in F2:

=COUNTIFS($A$2:$A$8,$E2,$B$2:$B$7,F$1)

which you can copy across and down.

On a system prior to 2007...

F2:

=SUMPRODUCT(--($A$2:$A$8=$E2),--($B$2:$B$7=F$1))