# Average of cells that match criteria and removing duplicates in Excel

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.

-

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.

-
I've been using Excel 2007 and only yesterday did I realize there's an AVERAGEIF function now. Amazing. – variant Aug 31 '10 at 11:56
I didn't know they had that now, either! Thanks for the heads up. – JNK Aug 31 '10 at 12:22

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".

-
how does one use a pivot table>? – Robert A. Fettikowski Aug 30 '10 at 17:31
Select data, then use Insert > PivotTable (assuming Excel 2007, may be different on other versions). – MisuVir Aug 30 '10 at 17:34
I think this is a better answer. – Sux2Lose Aug 30 '10 at 19:13

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:

-
This wouldn't accomplish the OP's wish to have each name only once, but I love your method. – JNK Aug 30 '10 at 17:36
sorry forgot to add the last part. – KronoS Aug 30 '10 at 17:43
Like this method. It leaves the data intact and gives the results how the OP needs them. Never destroy the original data...just move the results somewhere nicer to look at! – RocketGoal Aug 31 '10 at 8:57
Ya, I'm not sure which version have it, it appears that 2007+ do but not sure if prev do. I'm running 2010 – KronoS Aug 31 '10 at 13:07