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

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.

  • +1 good to see you here Thomas :) – brettdj Sep 25 '12 at 0:27
  • I don't see this option in Excel2010 – AYBABTU Sep 25 '12 at 1:51
  • In the toolbar, go to the Insert tab, then click the PivotTable button (it's the first button on the left). See Working with PivotTables in Excel for a guide. – Bavi_H Sep 25 '12 at 2:32
  • Impressively, Excel 365 successfully and quickly performed this on a dataset of 325,000 rows for me. – JD Smith Sep 21 '17 at 2:51

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)

  • This is not a solution if you have 10,000 students – AYBABTU Sep 25 '12 at 0:01
  • The number of students is really immaterial to this suggestion. This would only be unusable if the number of students was changing frequently. – Jesse Sep 25 '12 at 0:07
  • Depending on the use case either solution will work. If I had a fixed list of students and was adding scores through the year and reporting each period I would use a SUMIF table. If I wanted to do a one off piece of analysis I would stick to a pivot table. – DWGKNZ Sep 25 '12 at 0:18
  • What I understand is that I need to filter each student individually and then make the sum - "copy to another place". So if I have 10k students I will need to repeat this 10k times... – AYBABTU Sep 25 '12 at 1:50
  • The advanced filter allow you to create a complete distinct list of all your Student_IDs details on how to do this is here: (office.microsoft.com/en-us/excel-help/…) – DWGKNZ Sep 25 '12 at 2:13

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.

enter image description here

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: enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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