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)

--
Biff
Microsoft Excel MVP


"RJB" wrote in message
...
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?