6

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
Improve this question

3 Answers 3

Reset to default
5

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.

Improve this answer
4
  • +1 good to see you here Thomas :)
    – brettdj
    Sep 25, 2012 at 0:27
  • I don't see this option in Excel2010
    – AYBABTU
    Sep 25, 2012 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, 2012 at 2:32
  • Impressively, Excel 365 successfully and quickly performed this on a dataset of 325,000 rows for me.
    – JD Smith
    Sep 21, 2017 at 2:51
5

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)

Improve this answer
5
  • This is not a solution if you have 10,000 students
    – AYBABTU
    Sep 25, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 2:13
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .