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

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

=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


"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

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

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

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

Try with data from row6 to 12...Make sure there are no blanks

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

OR

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

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


"Tan" wrote:

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

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

"Bernd P" wrote:

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

.

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

You're a funny guy, Bernd!

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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