Hi,

I have a list of people who have attended our events in the past. In column
A, I have the names. In Column B, I have the city that they are from. I am
trying to create a list of how many people are from what area, but not
include repeated names. It's a little complicated because multiple cities go
into one area. I've figured out a way to do that - I'm using sumif/countif.
However, the number is too big because it will count someone who is from one
city that's gone to five different events five times.

To give an idea of what the spreadsheet looks like:

Joe Shanghai
Joe Shanghai
Joe Shanghai
Joe Shanghai
Mark Shanghai
Eric Houston
Ben DC
Ben DC
Max Beijing
Max Beijing
Alice Houston
Amy Houston
Amy Houston

So I need the output to look something like this:

China(Shanghai&Beijing): 3
US(Houston&DC):4

Would it also be the same way to do it if each city was represented by a
number, rather than text?


Thanks so much.