counting cells that contain a specific value

[Ed Cain]

I have a spreadsheet containing hundreds of business contact names,
addresses, etc., with the city and state in adjacent columns. I'm trying to
find a good way to easily and quickly count the number of contacts in a
particular city.

One obvious way might be to sort by city name and count the rows, being
careful not to count those contacts in a city with the same name, but a
different state -- e.g., Columbia MD vs. Columbia SC. There are a lots of
entries for each cities, so a manual count could easily be wrong.

"Countif" looks like it could be used to do this if I set up a formula, but
it's not clear how to do this when the state matters too. It's a more
complicated solution than I'm looking for, and it would require changing the
formula every time I want to count contacts in a different city.

Does anyone have a suggestion about how to do this simply? Thanks in
advance for any help.

 

[Ron Coderre]

Try something like this:

With
Cities listed in Col_A
States listed in Col_B

C1: (a city name)
D1: (a state)

This formula counts all combinations of C1_cities and D1_states:
E1: =SUMPRODUCT((A1:A1000=C1)*(B1:B1000=D1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Don Guillett]

try with this formula in b1
=countif(a:a,b2)
and the city name in b2