counting unique values in col A against unique value in column B

**greyscale** · 05-07-2013, 09:50 PM

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

**Tony Valko** · 05-07-2013, 09:56 PM

Try this...

With your data in the range A2:B8...

D2 = Sales
D3 = Service

Enter this array formula** in E2 and copy down:

=SUM(IF(FREQUENCY(IF(B$2:B$8=D2,MATCH(A$2:A$8,A$2:A$8,0)),ROW(A$2:A$8)-ROW(A$2)+1),1))

** 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.