Help Excel Formulae Help (counting unique items in a column with criteria)

[SheepCow]

A friend of mine has asked me a question, however my knowledge on Excel is letting me down (I can answer it in SQL, I have told him to get a real database!)

Basically he has a list of names and countries and wants to count the number of unique names from specific countries. Example:

Bob     UK
Frank   France
Bob     France
Steve   UK
Jill    France
Jane    UK
Bob     Germany

I want the number of unique names from the UK or Germany. So from that list we get Bob (UK), Steve (UK), Jane (UK), Bob (Germany) -- which gives us

Bob, Steve, Jane -- so 3 unique names.

How the poopsticks could this be done in Excel ?

Muchos thanks.

 

[djpringle]

Does the result need to be automatically updated when new names are added to the list or can the count be done via a macro?

 

[SheepCow]

I think he'd rather do it without any scripting/macros, I might give that a go though

 

[Vae]

There may be a more elegant way of doing it but could you just do the following:
Column A is Name
Column B is Country

In Column C have a formula: =IF(OR(B2="Germany",B2="UK"), A2,"")
which will return column A if column B matches UK or Germany. If you were wanting to carry this out for different countries you could point "Germany" and "UK" to specific cells which you then alter.

Then either:
A) filter the column for unique entries and then use a counta formula to count number of cells

OR

B) At the bottom of column C use the following formula to count the unique entries in column C
=SUM(IF(FREQUENCY(IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""), IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""))>0,1))

Note that once you have pasted this formula into a cell it won't work. You have to press F2 to edit the formula and then press Ctrl,Shift and then Enter to save it as an array formula.