How to add up all values in column F that has the same value in column A?

Question

i.e. if I'd like to sum the total score of a student from a sheet like this:

Student ID | Score 
  123456   |   12
  654789   |   14
  585822   |   5
  982531   |   22
  982531   |   32
  123456   |   17
  654789   |   14
  585822   |   18

I'd like to get back

Student ID | Score
  123456   |   29
  654789   |   28
  585822   |   23
  982531   |   54

Answer 1

Another way to achieve the same result is to create a pivot table based on your data: select your data, choose Insert Pivot Table, Finish. Use the Student ID as the row field and Score as the Data field.

Answer 2

You can do this by using SUMIF(range,criteria,sum_range)

You would need to create a list of distinct Student IDs which can be achieved with Advanced Filter (selecting 'Unique records only' and 'Copy to another place').

The formula would be =sumif(A:A,A2,F:F)

Answer 3

I don't know how to use Excel but I thought this was an interesting problem so I tried to figure out. Here is how I got it to work. It might not be the best way to do it.

Click Insert -> PivotTable. It will auto select what it thinks should be in the table. You can also manually select the data that you want to have in the pivot table.

Check the check boxes that you want to include in your pivot table (Student ID and Score).

Drag the Student ID from the fields list to the Row Labels box.

In the values box, click the down arrow for "Sum of Student ID" and click Remove Field.

At the end it may look like this:

You can right click on Grand Total and remove it if you don't want a grand total of the students scores. You can also sort by student ID by clicking on the down arrow that appears when you select the cell with the title for your student ID's and choose one of the sorting options.

Thanks for the test data. It was fun to play with.