Average of cells that match criteria and removing duplicates in Excel

Question

I have an Excel spreadsheet where I want to remove the duplicate names and take an average for each person's score. I have a list of the following type:

Al     1
Bob    1
Bob    2
Bob    1
Joe    4
Joe    2
Kevin  1
Kevin  8
Sally  5

For instance, Kevin should be 4.5 not 8 or 1 and only appear on my new list once.

Answer 1

You can do this with a combination of SUMIF and COUNTIF.

Assuming that your example table starts in A1:

=SUMIF(A1:A9,E1, B1:B9)/COUNTIF(A1:A9,E1)

That would give you the average of whatever name is in E1. You will need to type in the name list manually.

Answer 2

You could use a PivotTable. Add both fields to the PivotTable and change the value field setting to "average of" instead of the default "sum of".

Answer 3

Use the Subtotal command. First select the data you wish to "subtotal" and then select Data>>Subtotal:

Select ok, then select the function to be average:

click ok and then everything is done for you!

In order to show just the Averages of the user, simply look at the left hand side and click on the "negative" symbols to close the data in between: