Don't Count Duplicates

[Johnny]

Here's a subset of 3 colunms of data:

Zip Code Name County
42125 John Franklin
42156 John Fairfield
43123 Bill Fairfield
45612 Jane Fairfield
45126 Jane Franklin
49856 Jane Fairfield
45895 Chris Fairfiled
46289 Chris Fairfield
42194 Chris Fairfield
42312 Chris Franklin

I am looking for a formual to count the number of people assigned to each
Territory.

The answer based on thes sample data above would be:

Fairfield - 4
Franklin - 3

Thank you

 

[Teethless mama]

Criteria start in E2 and down

In F2: =SUM(N(FREQUENCY(IF(County=E2,MATCH(Name,Name,)),MATCH(Name,Name,))>0))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[T. Valko]

Try this array formula**.

B2:B11 = Name
C2:C11 = County

E2 = Fairfield
E3 = Franklin

Array entered** in F2 and copied down to F3:

=SUM(IF(FREQUENCY(IF(C$2:C$11=E2,MATCH(B$2:B$11,B$2:B$11,0)),ROW(B$2:B$11)-MIN(ROW(B$2:B$11))+1),1))

Assumes no empty cells in the Name range B2:B11.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.