SUMIF for an AVERAGE

[Guest]

I have a rather complex (at least to me) question... My worksheet has column
D with agent initials and column AE with a score. I'm trying to figure out
the formula to calculate an average score for each agent in column D based on
amount in AE?
Column D Column AE
AMJ 5
CLY 10
KRR 2
AMJ 5
KRR 3
DZL 2
etc., etc. Sometimes I'll have 100+ rows of information.

Any help is appreciated!

Tia B

 

[T. Valko]

One way:

Assume you have the unique agents listed in the range A2:A5

Enter this formula in B2 and copy down to B5:

=SUMIF(D$2$7,A2,AE$2:AE$7)/MAX(1,COUNTIF(D$2$7,A2))

Biff

 

[Guest]

One way ..

Source data in cols D and AE, assumed from row1 down, where
col D = agents
col AE = scores

In AG1:
=IF(D1="","",IF(COUNTIF($D$11,D1)>1,"",ROW()))

In AH1:
=IF(ROW()>COUNT(AG:AG),"",INDEX(D,SMALL(AG:AG,ROW())))
Select AG1:AH1, copy down to the max expected extent of source data, eg copy
down to AH200. This drives out a unique list of agents into col AH. with all
results neatly bunched at the top. Hide away col AG1 if desired.

Then to get the average for each agent in col AH, place in AI1 and
array-enter the formula, ie press CTRL+SHIFT+ENTER, instead of just pressing
ENTER:
=AVERAGE(IF($D$1:$D$200=AH1,$AE$1:$AE$200))
Copy AI1 down as far as required.

(Another way would be to use a pivot table)