[ms office - 2003] Excel - Averge value of a subset of results

[park_bench]

Calling all Excel gurus (or possibly just anybody with more nouse than I)...

 

Range_1 Range_2

a...............20

b...............10

c...............10

a...............10

c...............30

b...............20

 

I would like a formula that finds the average of results in 'Range_2' for all rows in 'Range_1' that equal "a".

 

I thought something like:

 

=AVERAGE(SUMPRODUCT((Range_1="a")*(Range_2))

 

However, this returns the sumproduct but not average. Any help would be very much welcomed.

 

Thanks a lot.

 

Ben

[park_bench]

=AVERAGE(SUMPRODUCT((Range_1="a")*(Range_2))

 

OK,

 

This works:

 

=SUMPRODUCT((Range_1="A")*(Range_2)/COUNTIF(Range_1,"A")

 

but is there a more elegant way?

 

Thanks all,

 

Ben