Counting Distinct Values

[Johnny]

I have three columns of data. Here's a subset:

ZIP Cope Name County
42312 John Franklin
42342 John Franklin
43545 John Franklin
42343 Mary Franklin
43786 Mary Franklin
48988 Dave Fairfield
47676 Dave Fairfield
47623 Mary Fairfiled
43589 Joe Fairfield
45646 Mike Fairfeld

I need a formula to count the number of people per county. The result for
this subset would be:

Franklin - 2
Fairfeld - 4

Thank you

 

[Bernard Liengme]

Here is an unsophisticated way. I added two columns
ZIP Cope Name County Test Count
42312 John Franklin JohnFranklin 1
42342 John Franklin JohnFranklin 2
43545 John Franklin JohnFranklin 3
42343 Mary Franklin MaryFranklin 1
43786 Mary Franklin MaryFranklin 2
48988 Dave Fairfield DaveFairfield 1
47676 Dave Fairfield DaveFairfield 2
47623 Mary Fairfield MaryFairfield 1
43589 Joe Fairfield JoeFairfield 1
45646 Mike Fairfield MikeFairfield 1
The Test column has =B2&C3 in D2 and this is copied down the column
For the Count in E2, I used =COUNTIF($D$22,D2) --- watch the mixed
absolute and relative references) and copied down
Starting in GF2 (could be on another sheet) I made a list of county names
(you could use Advanced Filter to get a list with unique names)
County Census
Franklin 2 formula is
SUMPRODUCT(--($C$2:$C$11=G2),--($E$2:$E$11=1))
Fairfield 4
best wishes

 

[Bob Phillips]

Try this array formula

=COUNT(1/IF($C$2:$C$100="Franklin",MATCH($B$2:$B$100,$B$2:$B$100,0)=ROW($B$2:$B$100)-ROW($B$2)+1))

 

[T. Valko]

see your other post

 

[Bernd P]

Hello John,

I suggest to use my UDF Pfreq:
http://sulprobil.com/html/pfreq.html

Regards,
Bernd