Counting Unique occurences of text in a column

  • Thread starter Thread starter Tan
  • Start date Start date
T

Tan

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
 
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
 
Hello,

Since you will use any solution for your employer and not just for fun
I strongly suggest to use only
a) a solution you really understand
b) a solution which you think is easily to maintain

I suggest to use
=COUNT(Pstat("Count",(C2:C9="Y")*(B2:B9="B"),A2:A9))
My UDF Pstat you can find here:
http://sulprobil.com/html/pstat.html

The most reasonable long-term approach might be a pivot table, though.

Regards,
Bernd
 
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
 
=SUM(N(FREQUENCY(IF((C2:C8="Y")*(B2:B8="B"),MATCH(A2:A8,A2:A8,)),MATCH(A2:A8,A2:A8,))>0))

Ctrl+Shift+Enter, not just Enter
 
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.
 
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?
 
Try with data from row6 to 12...Make sure there are no blanks

=SUM(--(FREQUENCY(IF((B6:B12="B")*(C6:C12="Y"),MATCH(A6:A12,A6:A12,0)),ROW(A6:A12)-ROW(A6)+1)>0))

OR

=SUM(--(FREQUENCY(IF((B6:B12="B")*(C6:C12="Y"),MATCH(A6:A12,A6:A12,0)),ROW(INDIRECT("1:"&ROWS(A6:A12))))>0))

If this post helps click Yes
 
Hello Tan,

That ROW(INDIRECT()) construct creates a simple array:
{1;2;3;4;5;6;7;8}
You can evaluate a more complex formula partially by entering the
formula editor, selecting the part of your interest and pressing F9.

I hope you realize that the worksheet formulae suggested so far would
be leading you up the garden path.

If you have difficulties to understand and to implement them, you can
be sure that a third person in your company would struggle with them
later, too.

Regards,
Bernd
 
Hello Biff,

I would have hoped for a more responsible answer from you. You read
that a solution would be used for a company and you have seen
Herbert's and my answer already, haven't you?

Regards,
Bernd
 
Bernd

If you review the initial post the OP is trying to work out a formula using
SUM() and FREQUENCY() and hence the formula way...
 
Back
Top