Hi,

I came across this thread searching for an answer to my problem:

http://www.excelforum.com/excel-gene...ue-values.html

It suggests the following formula if data is in A2:C8, and input criteria are in E2 and F2
=COUNT(1/FREQUENCY(IF($A$2:$A$8=E2,IF($B$2:$B$8=F2,IF($C$2:$C$8<>"",MATCH($C$2:$C$8,$C$2:$C$8,0)))),ROW($C$2:$C$8)-ROW($C$2)+1))

This will not work for me as, while I could put a fixed dept value in F2, E2 in my example is not a fixed value. It is a person's name and is constantly changing. Basically, I have the following data (simplified greatly. There are other columns causing these duplicates e.g. details of role type):

A (name)- B (dept)
Mary - Sales
Mary - Service
Mary - Service
John - Sales
John - Sales
Peter - Sales
Peter - Service

I need a formula that can populate a single cell counting the number of unique people in each dept (ignoring duplicate such as Mary appearing twice under Service). eg.
Sales = 3
Service = 2

Can anyone suggest a way to do this? Thanks in advance