Count Number of Discrete Cells, Based on Another Column

[RJB]

I've tried the Google and many solutions, and keep coming up with wrong
answers.

I have a list of customers, the types of merchandise they buy, and how much
they spent on each product.

I used SUMPRODUCT to calculate how much is spent on each product.
(=SUMPRODUCT (Merchandise Column="criteria"*Revenue Column)

Now I want to know what the average spend for each customer is.

Here's where it gets tricky: A customer can buy the same type of merchandise
several times.

So a COUNTIF does not work - that tells me average spend per transaction for
each type of merch, not by customer.

I tried SUMPRODUCT (Merchandise Column="Criteria"*Revenue Column*Customer
Column), that gave me an error.

I tried replacing Customer Column in the above with
SUM(IF(FREQUENCY(MATCH(Customer Column,Customer Column,0),MATCH(Customer
Column,Customer Column,0))>0,1)). THAT multiplied times the TOTAL number of
discrete customers, not just the ones that bought this type of merchandise.

I tried a couple of other things uglier than THAT.

Please help.

Thanks

(For those who prefer examples, one follows)

SAMPLE DATA:
(Customer, Merchandise,Revenue)
Bill, Yo-Yo, 5
Charlie, Guitar, 50
Dora, Lipstick, 10
Efrem, Pomade, 6
Fred, Yo-Yo, 5
Bill, Eyeliner, 7
George, Guitar, 65
Bill, Yo-Yo, 5

So, for Yo-Yo, I'd have
SUMPRODUCT(Merch Column="Yo-Yo"*Revenue Column)
=$15

What I'd like is average customer spend on Yo-Yos. I have two Yo-Yo
customers - Bill and Fred. So $15/2 = $7.50.

How do I get the count of Bill and Fred?

 

[T. Valko]

Try this array formula...(assumes no empty cells with the ranges)

B2:B9 = product
C2:C9 = amount
E2 = Yo-Yo

=SUMIF(B2:B9,E2,C2:C9)/COUNT(1/FREQUENCY(IF(B2:B9=E2,MATCH(A2:A9,A2:A9,0)),ROW(A2:A9)-MIN(ROW(A2:A9))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[RJB]

Worked perfectly. Thanks.

If you have a moment to explain what's happening here, I'd appreciate it.

I thought I understood the frequency/match thing, but not why it's an "If",
and I certainly don't understand taking it as a reciprocal.

Thanks!

 

[RJB]

Additionally, can I add a fourth dimension?

D29 = color

Now I want to know average customer spend on RED Yo-Yo's?????

Thanks

 

[T. Valko]

Try this array formula** :

E2 = product = Yo-Yo
E3 = color = red

=SUMPRODUCT(--(B2:B9=E2),--(D29=E3),C2:C9)/COUNT(1/FREQUENCY(IF((B2:B9=E2)*(D29=E3),MATCH(A2:A9,A2:A9,0)),ROW(A2:A9)-MIN(ROW(A2:A9))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

It's a little too late in the night (2:00 AM) to write out an explanation.
I'll do it tomorrow when I have more time. I tend to write really long
winded explanations and it takes me awhile because I'm a "1 finger" typer!

 

[RJB]

Thanks.

Incidentally, what's the deal with the dashes?

I put in the formula like this:
=SUMPRODUCT((B2:B9=E2)*(D29=E3)*C2:C9)

While you do it thusly:
=SUMPRODUCT(--(B2:B9=E2),--(D29=E3),C2:C9)

 

[T. Valko]

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 look at 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!



exp101

 

[T. Valko]

See these:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html