Assuming no empty cells in column A...

Array entered** :

=SUM(IF(FREQUENCY(IF(C2:C8="Y",IF(B2:B8="B",MATCH( A2:A8,A2:A8,0))),ROW(A2:A8)-ROW(A2)+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.

--
Biff
Microsoft Excel MVP


"Tan" wrote in message
...
Hi all, i needs help urgently to finish up my company report but encounter
the following problems..

In my summary tab, i m trying to count the unique occurence of my sales
rep
names in column A, where the customer falls in a particular segment
A/B/C/D
and coverage must be equal to "Y".

My database example as follows:

Column A Column B
Column C
Sales rep name Segmentation of customer
2009 Coverage
Barry B
Y
Leane B
Y
Barry B
Y
Barry A
Y
Aisiling B
Y
Leane B
Y
Dave B
Y

Based on above data, i want to analyse how many sales rep headcount are
serving those customers under segmentation B and also 2009 coverage must
be
equal to "Y".

So, if i were to look at segment B and coverage equal to "Y", i shall see
4
reps. 4 reps becos i have Barry, Leane, Dave and Aisiling serving
customers
accounts under segment B and coverage equal to "Y".

I have tried to use below formula but always got a result of zero. Think
its
becos the formula can only count unique values and not text cells.

=SUM(--(FREQUENCY(IF((B2:B8="B")*(C2:C8="Y"),A2:A8),A2:A8 )0))

Can any guru advice me a workaround to resolve my problem? thanks