Comparing two excel columns to get a count

excel_dummie

New Member
Joined
Mar 26, 2011
Messages
1
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>
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,353
Messages
5,528,203
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top