On Mar 25, 10:50*am, Roger Govier
wrote:
Hi

Ziggy is quite right.
My apologies, I did not read the question properly.

The following will produce the answer you want.

On Sheet1 add the following formula in C2 and copy down
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,"",
SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)))

Then on Sheet 2 in B2 enter the following
=SUMIF(Sheet1!B:B,Sheet2!A2,Sheet1!C:C)
and copy down

--
Regards
Roger Govier



Ziggy wrote:
I don't believe that Roger's formula will work, It will result in
3,2,1 not the 2,1,1.

I don't have an elegant single formula solution. My solution is for
2007. For 2003 you would need an array formula. Her goes.

In a new column, add the two columns: *= A2&B2 for the entire list.

Copy and PasteSpecial *Values into a new column.

2007 allows you to remove Duplicates in the Data tab. *Else, sort
alphabetically, and use an IF formula to compare consecutive fields.

=IF(G2=G3,2,1); then eliminate all the 2's

Once you have unique fields, new column =right(H2,6) will give you the
cities.

Then do the =countif(Column,Reference)

Not elegant but the result is the number of unique person/city
combinations- Hide quoted text -

- Show quoted text -

Nice solution Biff.