Hi Jacob,

what is the "1:" in that ******ROW(INDIRECT("1:"&*******means? I m not too
sure. Its at the back of your array:

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

If my data row starts at row 6, what should i do?

"Jacob Skaria" wrote:

Incase you are unfamiliar with array formulas...

An array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

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


"Jacob Skaria" wrote:

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