Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
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".

share|improve this answer
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:

alt text

Select ok, then select the function to be average:

alt text

click ok and then everything is done for you!

alt text

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:

alt text

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.