Let's use this smaller data sample to see how this works:
Bill..........Yo-Yo......5
Charlie.....Guitar....50
Fred........Yo-Yo......5
Efrem......Pomade...6
Bill..........Yo-Yo......5
E2 = Yo-Yo
Let's use just this portion of the formula:
COUNT(1/FREQUENCY(IF(B2:B6=E2,MATCH(A2:A6,A2:A6,0)),ROW(A2:A6)-MIN(ROW(A2:A6))+1))
Everything is dependent upon the product being a Yo-Yo. So we use the conditional IF and MATCH to determine what our data_array will be for use in the FREQUENCY function. We use the ROW()-MIN(ROW())+1 expression to determine what the bins will be for use in the FREQUENCY function
With the conditional test, IF(B2:B9=E2 (product = Yo-Yo), we get an array of TRUE or FALSE.
B2 = E2 = T
B3 = E2 = F
B4 = E2 = T
B5 = E2 = F
B6 = E2 = 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. Also, MATCH will find only the first instance of the lookup value. The lookup values are the customer names and the lookup array is also the customer names. Where the conditional IF is FALSE the MATCH function will also return FALSE. So, this is the array generated by the MATCH function:
MATCH(A2,B2:B6,0) = 1
MATCH(A3,B2:B6,0) = F
MATCH(A4,B2:B6,0) = 3
MATCH(A5,B2:B6,0) = F
MATCH(A6,B2:B6,0) = 1
Notice how there are 2 instances of 1. This is because both A2 and A6 contain Bill and as I mentioned MATCH will only find the first instance of the lookup value.
This is the data array used in the FREQUENCY function:
{1,F,3,F,1}
Now we need to generate the bins array.
Since MATCH returns *relative* positions the bins array needs to be an array of numbers that correspond to these relative postions. Based on this sample data the only possible *numbers* that can be returned by MATCH are 1,2,3,4,5 (or the logical FALSE). So, the bins array must also contain these numbers. We use this expression to get them:
ROW(A2:A6)-MIN(ROW(A2:A6))+1
Here's how we get the bins numbers:
ROW(A2)-MIN(ROW(A2))+1 = 1
ROW(A3)-MIN(ROW(A2))+1 = 2
ROW(A4)-MIN(ROW(A2))+1 = 3
ROW(A5)-MIN(ROW(A2))+1 = 4
ROW(A6)-MIN(ROW(A2))+1 = 5
So, we now have a bins array that looks like this:
{1,2,3,4,5}
At this point this is what the FREQUENCY function looks like:
FREQUENCY{1,F,3,F,1},{1,2,3,4,5}
FREQUENCY ignores logicals so these are the frequencies that are calculated:
{2,0,1,0,0,0}
Two 1s and one 3.
Now we're getting pretty close to the end result!
The result of the FREQUENCY function is passed to the COUNT function. However, COUNT counts all numbers but we don't want it to count the 0s. We only want the count of numbers that are >0. Here's how we do that:
COUNT(1/{2,0,1,0,0,0})
1 / 2 = 0.5
1 / 0 = #DIV/0!
1 / 1 = 1
1 / 0 = #DIV/0!
1 / 0 = #DIV/0!
1 / 0 = #DIV/0!
So, now the COUNT function looks like this:
COUNT(0.5,DIV,1,DIV,DIV,DIV)
So:
COUNT = 2
Meaning, there are 2 unique customers that bought Yo-Yo's!
Bookmarks