Lookup and Return Average

**cecilj** · 12-22-2009, 02:36 PM

I want to have a dropdown list in column A(payee). Column B I will enter how much money was paid out. What i want is a seperate Spreadsheet on the same page that shows the average amount paid to that payee, and i guess if possible to have it show the total paid out for the year sort of thing. I have searched through the forums but can't seem to find anything. I'm sure its there but I can't seem to find it lol

Thanks for the help

**NBVC** · 12-22-2009, 03:44 PM

So if you have the payees listed, say in column X, starting at X2, then in Y2

=AVERAGEIF($A$1:$A$100,X2,$B$1:$B$100)

where A1:A100 contains your selected names, and B1:B100 shows amounts paid.

Adjust ranges to suit.

Total would be similar but use Sumif()

=SUMIF($A$1:$A$100,X2,$B$1:$B$100)

**cecilj** · 12-23-2009, 12:17 PM

Works Great!! the only problem i have which is a minor one is i get a #DIV0! error in the box if there are blanks on the payee section. is there a way to fix this?

**NBVC** · 12-23-2009, 12:22 PM

Perhaps

=IF(COUNTA($A$1:$A$100)>0,AVERAGEIF($A$1:$A$100,X2,$B$1:$B$100),"")

**cecilj** · 12-23-2009, 12:45 PM

That didn't work got the same problem with the #DIV!0 error. Here is my exact formula
the SUMIF works perfectly fine using the above formula you suggested.

=AVERAGEIF($A$29:$A$55,J30,$C$29:$C$55)

this is the new one you suggested with my adjustments

=IF(COUNTA($A$29:$A$55)>0,AVERAGEIF($A$29:$A$55,J30,$B$29:$B$55),"")