Try array formula..

=SUM(--(FREQUENCY(IF((B1:B8="B")*(C1:C8="Y"),MATCH(A1:A8, A1:A8,0)),ROW(INDIRECT("1:"&ROWS(A1:A8))))0))

If this post helps click Yes
---------------
Jacob Skaria


"Tan" wrote:

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