Average percentages

[Powick]

Hi

This is my first post so go easy!

I'm trying to return an average percentage based on different types of criteria. I want a formula to lookup a name first, then a product and return the average percent in another column.

My current list looks like this:
Name Product Percentage
Joe Bloggs Peanuts 88%
Joe Black Peanuts 92%
Joe Bloggs Raisins 80%
Joe Bloggs Peanuts 90%

How do I get the average percentage for 'Joe Bloggs' and 'Peanuts'. I'm using Excel 2003.

Thanks in advance for your help.

Powick

 

[Desu Nota from Columbus]

Assuming the Columns are A-C, put this in column D

=SUMPRODUCT(--($A$1:$A$4="Joe Bloggs"),--($B$1:$B$4="Peanuts"),$C$1:$C$4)/SUMPRODUCT(--($A$1:$A$4="Joe Bloggs"),--($B$1:$B$4="Peanuts"))


Change the bounds of the arrays to match your data (you gave me only 4 records so the arrays are A1:A4, B1:B4, C1:C4)

 

[JimM]

How about

=SUMPRODUCT(--(A1:A4="Joe Bloggs"),--(B1:B4="Peanuts"),--(C1:C4))/SUMPRODUCT(--(A1:A4="Joe Bloggs"),--(B1:B4="Peanuts"))

 

[Powick]

Thank you both guys, this has really helped me out of a pickle!

Excellent forum!

Powick

 

[Jenny_Genesis]

Hey,
Other than excel own formula, is there any Vba code for this..

Best regards,
Jenny_Genesis