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
