Unique Values

I'm hoping a formula can do this, as I can’t use a script in this instance, but I can't seem to figure it out if it is possible.

I have two columns of data, a listing of names and a list of numbers (there is other data, but only these two are important. For example

number, name, area
1, John, 1
2, John, 1
3, Bill, 1
4, Sam, 2
5, Sam, 2

I need the number of unique names in an area, in this instance area 1 would be 2 (John and Bill). I just need the count. It is likely that each unique name will only be in one area, so in this instance, John would not have a record further down with area 2, only 1.

I have been able to figure out how to get a count of unique names, but I can’t correlate it per area.

My hope is for someone with little excel experience to paste information in another sheet, then look at the summary sheet to get statistics.

With names in B2:B10 and areas in C2:C10 you could get the number of different names for area 2 with this formula

=SUM(IF(C2:C10=2,1/COUNTIFS(C2:C10,2,B2:B10,B2:B10)))

confirmed with CTRL+SHIFT+ENTER

Assuming:

1) the Names are in A1:A100
2) the Areas are in B1:B100
3) You enter the "area" you want to count in D1

This array formula in E1 will give you count of unique names matching the area noted in D1:

=SUM(IF(FREQUENCY(IF($B$1:$B$100=$D1,MATCH($A$1:$A$100,$A$1:$A$100,0)),ROW($A$1:$A$100)-ROW($A$1)+1),1))

...that formula is confirmed by pressing CTRL-SHIFT-ENTER to activate the array.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.