Counting Unique occurences of text in a column

[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

 

[Jacob Skaria]

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

 

[Bernd P]

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

 

[Jacob Skaria]

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

 

[Teethless mama]

=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

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
As suggested by Bernd.
No formulas of any kind.
No VBA code.
http://www.mediafire.com/file/n2uznmn02jd/10_25_09.xlsx

 

[T. Valko]

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.

 

[Tan]

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]

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

 

[Bernd P]

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

 

[Bernd P]

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

 

[Jacob Skaria]

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

 

[T. Valko]

I would have hoped for a more responsible answer from you.

You're a funny guy, Bernd!