This feels like it should be quite simple, but I'm really struggling...

I have a list of quotes all made by my salespeople, each one submits quotes
for selling items to customers, sometimes the same customer will ask for
quotes on 10 or 15 different items.

for example, Fred has quoted a price (to Builders Inc) for bricks, another
price for tiles, and another price for concrete, all for the same customer.

These three items show up as three separate lines in my list, but each one
of these three lines has the same customer name on it

Fred has also quoted for other people and other sales people might have also
quoted for stuff for 'Builders Inc' too

So here's waht I'm after...
I want a table of all my sales people, and next to each sales persons name,
I just want the number of DIFFERENT customers they've quoted to, so for
Fred, 'Builders Inc' must only show as one (even though it appears three
times). I want a total number of unique customes that each sales person has
had dealings with.

I hope that makes sense

Thank you

M

Try this array formula** :

A1:A20 = sales person
B1:B20 = customer

D1 = sales person

Array entered** in E1:

=SUM(IF(FREQUENCY(IF(A$1:A$20=D1,MATCH(B$1:B$20,B$ 1:B$20,0)),ROW(B$1:B$20)-ROW(B$1)+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.

Assumes no empty cells in the customer range.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
This feels like it should be quite simple, but I'm really struggling...

I have a list of quotes all made by my salespeople, each one submits
quotes for selling items to customers, sometimes the same customer will
ask for quotes on 10 or 15 different items.

for example, Fred has quoted a price (to Builders Inc) for bricks, another
price for tiles, and another price for concrete, all for the same
customer.

These three items show up as three separate lines in my list, but each one
of these three lines has the same customer name on it

Fred has also quoted for other people and other sales people might have
also quoted for stuff for 'Builders Inc' too

So here's waht I'm after...
I want a table of all my sales people, and next to each sales persons
name, I just want the number of DIFFERENT customers they've quoted to, so
for Fred, 'Builders Inc' must only show as one (even though it appears
three times). I want a total number of unique customes that each sales
person has had dealings with.

I hope that makes sense

Thank you

M

Hi
If you have the data in say 3 columns
Column A - Sales Rep
Column B - Company Quoted
Column C - Details of what they quoted

You could easily summarise in a pivot table and get even more comprehensive
set of analysis also.
In the Pivot you can count Company quoted by Sales Rep

Thanks
Matt



Michelle wrote:
This feels like it should be quite simple, but I'm really struggling...

I have a list of quotes all made by my salespeople, each one submits quotes
for selling items to customers, sometimes the same customer will ask for
quotes on 10 or 15 different items.

for example, Fred has quoted a price (to Builders Inc) for bricks, another
price for tiles, and another price for concrete, all for the same customer.

These three items show up as three separate lines in my list, but each one
of these three lines has the same customer name on it

Fred has also quoted for other people and other sales people might have also
quoted for stuff for 'Builders Inc' too

So here's waht I'm after...
I want a table of all my sales people, and next to each sales persons name,
I just want the number of DIFFERENT customers they've quoted to, so for
Fred, 'Builders Inc' must only show as one (even though it appears three
times). I want a total number of unique customes that each sales person has
had dealings with.

I hope that makes sense

Thank you

M

--
Matt Lynn

Message posted via http://www.officekb.com

That's great - thanks. How can I find out how it works, so that I can do
similar things without asking for help in future?

M


"T. Valko" wrote in message
...
Try this array formula** :

A1:A20 = sales person
B1:B20 = customer

D1 = sales person

Array entered** in E1:

=SUM(IF(FREQUENCY(IF(A$1:A$20=D1,MATCH(B$1:B$20,B$ 1:B$20,0)),ROW(B$1:B$20)-ROW(B$1)+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.

Assumes no empty cells in the customer range.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
This feels like it should be quite simple, but I'm really struggling...

I have a list of quotes all made by my salespeople, each one submits
quotes for selling items to customers, sometimes the same customer will
ask for quotes on 10 or 15 different items.

for example, Fred has quoted a price (to Builders Inc) for bricks,
another price for tiles, and another price for concrete, all for the same
customer.

These three items show up as three separate lines in my list, but each
one of these three lines has the same customer name on it

Fred has also quoted for other people and other sales people might have
also quoted for stuff for 'Builders Inc' too

So here's waht I'm after...
I want a table of all my sales people, and next to each sales persons
name, I just want the number of DIFFERENT customers they've quoted to, so
for Fred, 'Builders Inc' must only show as one (even though it appears
three times). I want a total number of unique customes that each sales
person has had dealings with.

I hope that makes sense

Thank you

M

Hello,

Or array-enter into a sufficiently long area with 2 columns:
=Pfreq(Pfreq(A1:A20,B1:B20))

Pfreq you can get he
http://sulprobil.com/html/pfreq.html

Regards,
Bernd

I don't know of a resource that I can direct you to where this type of
formula is explained. I can explain it in great detail but right now I don't
have the time (I'm the worlds slowest typist). I'll post an explanation
later tonight.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
That's great - thanks. How can I find out how it works, so that I can do
similar things without asking for help in future?

M


"T. Valko" wrote in message
...
Try this array formula** :

A1:A20 = sales person
B1:B20 = customer

D1 = sales person

Array entered** in E1:

=SUM(IF(FREQUENCY(IF(A$1:A$20=D1,MATCH(B$1:B$20,B$ 1:B$20,0)),ROW(B$1:B$20)-ROW(B$1)+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.

Assumes no empty cells in the customer range.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
This feels like it should be quite simple, but I'm really struggling...

I have a list of quotes all made by my salespeople, each one submits
quotes for selling items to customers, sometimes the same customer will
ask for quotes on 10 or 15 different items.

for example, Fred has quoted a price (to Builders Inc) for bricks,
another price for tiles, and another price for concrete, all for the
same customer.

These three items show up as three separate lines in my list, but each
one of these three lines has the same customer name on it

Fred has also quoted for other people and other sales people might have
also quoted for stuff for 'Builders Inc' too

So here's waht I'm after...
I want a table of all my sales people, and next to each sales persons
name, I just want the number of DIFFERENT customers they've quoted to,
so for Fred, 'Builders Inc' must only show as one (even though it
appears three times). I want a total number of unique customes that each
sales person has had dealings with.

I hope that makes sense

Thank you

M

Thanks - this is very useful

M


"Bernd P" wrote in message
...
Hello,

Or array-enter into a sufficiently long area with 2 columns:
=Pfreq(Pfreq(A1:A20,B1:B20))

Pfreq you can get he
http://sulprobil.com/html/pfreq.html

Regards,
Bernd

We want to count the unique customers for salesperson Joe. Let's use this
smaller data sample to see how this works:

........A............B...............D
1...Joe.....CustomerA......Joe
2...Sue....CustomerB
3...Tom....CustomerC
4...Joe.....CustomerA
5...Joe.....CustomerB

=SUM(IF(FREQUENCY(IF(A$1:A$5=D1,MATCH(B$1:B$5,B$1: B$5,0)),ROW(B$1:B$5)-ROW(B$1)+1),1))

Result = 2

Everything is dependent upon the salesperson being Joe. So we use the IF
function: IF(A$1:A$5=D1 (salesperson = Joe), to test the range A1:A5 for
Joe.

With this test we get an array of TRUE or FALSE.

A1 = D1 = T
A2 = D1 = F
A3 = D1 = F
A4 = D1 = T
A5 = D1 = T

Where this condition is TRUE we use MATCH to generate an array of values
that we will then use to get the count of the frequencies from.

MATCH returns the relative position of the lookup value within the lookup
array. MATCH will find only the first instance of the lookup value when
there are duplicate lookup values. The lookup values are the customer names
and the lookup array is also the customer names.

MATCH(CustomerA,B1:B5,0) = 1
MATCH(CustomerB,B1:B5,0) = 2
MATCH(CustomerC,B1:B5,0) = 3
MATCH(CustomerA,B1:B5,0) = 1
MATCH(CustomerB,B1:B5,0) = 2

Notice how there are 2 instances of 1 and 2 instances of 2. This is because
of the duplicate lookup values CustomerA and CustomerB and that MATCH will
only find the first instance of a lookup value.Where the IF function test is
TRUE: IF(A$1:A$5=D1, the result of the IF function will be the corresponding
result of the MATCH function. Where the IF function test is FALSE the result
will also be FALSE:

T..1 = 1
F..2 = F
F..3 = F
T..1 = 1
T..2 = 2

This array is passed to the FREQUENCY function and is the data array
argument of the FREQUENCY function. The FREQUENCY function does a series of
"count if's". It counts how many items in the data array meet certain
conditions. In this case, those conditions are a series of numbers called
the bins array. Since MATCH returns a series of specific numbers we want to
count just how many of each of those specific numbers there are. Since the
range we want to count is B1:B5 (5 rows), that means the result of MATCH
could be a number from 1 to 5. So, we want to count how many 1s, 2s, 3s, 4s
and 5s are in the data array. So, we need to tell the FREQUENCY function we
want to count the numbers 1,2,3,4,5. We do this using this expression:

ROW(B$1:B$5)-ROW(B$1)+1

That expression will return the array 1;2;3;4;5

ROW(B1)-ROW(B1)+1 = 1
ROW(B2)-ROW(B1)+1 = 2
ROW(B3)-ROW(B1)+1 = 3
ROW(B4)-ROW(B1)+1 = 4
ROW(B5)-ROW(B1)+1 = 5

At this point this is what the FREQUENCY function looks like:

FREQUENCY{1;F;F;1;2},{1,2,3,4,5}

As I mentioned above, FREQUENCY returns a series of "count if's" on the data
array based on the values of the bins array. These are those "count if's" :

count if data array is <=1
count if data array is 1 and <=2
count if data array is 2 and <=3
count if data array is 3 and <=4
count if data array is 4 and <=5
count if data array is 5

And these are the results of those "count if"s":

{1;F;F;1;2} <=1 = 2
{1;F;F;1;2} 1 and <=2 = 1
{1;F;F;1;2} 2 and <=3 = 0
{1;F;F;1;2} 3 and <=4 = 0
{1;F;F;1;2} 4 and <=5 = 0
{1;F;F;1;2} 5 = 0

The logical FALSE is ignored.

Now we're getting pretty close to the end result!

The results of the FREQUENCY function are then passed to the outer IF
function. At this point the formula looks like this:

=SUM(IF({2;1;0;0;0;0},1))

We take advantage of a little known "trick" about the IF function.
Typically, an IF function is made up of 3 arguments:

logical test
value if TRUE
value if FALSE

The logical test is usually a test for some condition. For example, the
inner IF is using the logical test of: A$1:A$5=D1. It's testing each cell in
the range A1:A5 to see if it equals what's in cell D1. The result of this
test is either an array of TRUE or FALSE. If the result is TRUE then the IF
function returns the value if TRUE argument. If the result is FALSE then the
IF function returns the value if FALSE argument.

In Excel, numbers can also be used to represent TRUE and FALSE. Typically, 1
= TRUE and 0 = FALSE. However, in Excel *any number other than 0* will be
evaluated as TRUE. So, with our formula now looking like this:

=SUM(IF({2;1;0;0;0;0},1))

We get this:

IF 2 = T
IF 1 = T
IF 0 = F
IF 0 = F
IF 0 = F
IF 0 = F

Where the logical test is TRUE, return the value if TRUE argument of 1.
Where the logical test is FALSE, return the value if FALSE argument of
_____. Ah, now what? We haven't defined a value if FALSE argument. Since we
haven't done that the IF function will return the default value if FALSE
argument of FALSE.

IF 2 = T = 1
IF 1 = T = 1
IF 0 = F = F
IF 0 = F = F
IF 0 = F = F
IF 0 = F = F

These results are then passed to the SUM function and summed to get our
FINAL result:

=SUM({1;1;F;F;F;F})

=2

So:

Count the unique customers for salesperson Joe.

=SUM(IF(FREQUENCY(IF(A$1:A$5=D1,MATCH(B$1:B$5,B$1: B$5,0)),ROW(B$1:B$5)-ROW(B$1)+1),1))

=2




exp101
--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I don't know of a resource that I can direct you to where this type of
formula is explained. I can explain it in great detail but right now I
don't have the time (I'm the worlds slowest typist). I'll post an
explanation later tonight.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
That's great - thanks. How can I find out how it works, so that I can do
similar things without asking for help in future?

M


"T. Valko" wrote in message
...
Try this array formula** :

A1:A20 = sales person
B1:B20 = customer

D1 = sales person

Array entered** in E1:

=SUM(IF(FREQUENCY(IF(A$1:A$20=D1,MATCH(B$1:B$20,B$ 1:B$20,0)),ROW(B$1:B$20)-ROW(B$1)+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.

Assumes no empty cells in the customer range.

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
This feels like it should be quite simple, but I'm really struggling...

I have a list of quotes all made by my salespeople, each one submits
quotes for selling items to customers, sometimes the same customer will
ask for quotes on 10 or 15 different items.

for example, Fred has quoted a price (to Builders Inc) for bricks,
another price for tiles, and another price for concrete, all for the
same customer.

These three items show up as three separate lines in my list, but each
one of these three lines has the same customer name on it

Fred has also quoted for other people and other sales people might have
also quoted for stuff for 'Builders Inc' too

So here's waht I'm after...
I want a table of all my sales people, and next to each sales persons
name, I just want the number of DIFFERENT customers they've quoted to,
so for Fred, 'Builders Inc' must only show as one (even though it
appears three times). I want a total number of unique customes that
each sales person has had dealings with.

I hope that makes sense

Thank you

M