adding up times x occurs, excluding if y repeats in a different co

[h20polo]

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.

 

[Bob Phillips]

=SUM(--(FREQUENCY(IF(B1:B100={"Beijing","Shanghai"},MATCH(A1:A100,A1:A100,0)),ROW(INDIRECT("1:"&ROWS(A1:A100))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)