## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

# How to sum col2 when col1 changes and then order in descending totals

Posted on 2012-04-10
485 Views
I have a spreadsheet that has studentname in column 1, and scolarship amount in column2.
One student can get multiple scolarships.  I need to be able to sum column2 for each student, and the order the list in decending order of of each student's total

studenta         1,000
studenta            300
studenta            200
studentb            500
studentc          1,000
studentc             700

desired results:

studentc          1,700
studenta          1,500
studentb             500
0
Question by:abgail
• 5
• 3
• 2
• +2

LVL 42

Expert Comment

ID: 37828940
If your data in column A starts in row 2, and you have, say 100 students (just change the 100 to the size of the row, or use A:A which is less efficient, but will include all the rows):

In F2:F4, you have:

studentc
studenta
studentb

Then in D2, you can use the formula:

[D2]=SUMIF(\$A\$2:\$A\$100,\$F2,\$B\$2:\$B\$100)

and copy down for all your student names in column F.

See attached.

Dave
studentScholarships-r1.xls
0

LVL 13

Expert Comment

ID: 37828941
0

LVL 42

Expert Comment

ID: 37828949
I was editing mine and couldn't submit.  now that you have the pivot option suggested....

Shanan212 - want to use my example, add a pivot table and put the sort in?

Cheers,

Dave
0

LVL 33

Expert Comment

ID: 37831826
Alternative, certainly works in 2003.

Sort the data initially by student and then use the Subtotals wizard to add subtotals to the Amount column.

This will also add Outline Grouping icons down the left hand margin.

Collapse all the groups to show only Subtotals. Now select all the data and apply a Sort by amount. The groups will be moved as a block rather than individual lines.

Thanks
Rob H
0

LVL 9

Expert Comment

ID: 37834486
I'll pick up the pivot table invite using your file dave,
xls is also attached.

ugh, forgot the sort, back in a minute

Anthony
0

Author Comment

ID: 37834536
Using the pivot table, is there a way to sort by total?
0

LVL 9

Expert Comment

ID: 37834549
yes see new image above and file attached here, you beat me to it while I was fixing that little matter.
studentScholarships-r2.xls
0

Author Comment

ID: 37834623
I've requested that this question be closed as follows:

Accepted answer: 0 points for abgail's comment #37834536

for the following reason:

This solution solved my problem.
0

LVL 9

Accepted Solution

Anthony Mellor earned 500 total points
ID: 37834583
Here's how you find the sort option, it's a bit buried, shout if you need guidance for a different version, this is from Excel 2008.
0

LVL 9

Expert Comment

ID: 37834606
abgail, I think you hit the wrong button there :-)
I'll ask a mod to pop in to help... when I figure out how..
Anthony
0

LVL 9

Expert Comment

ID: 37834624
I think abgail has pressed the wrong button, having done that myself on occasion. Anthony
0

Author Comment

ID: 37834631
Yes, I hit the wrong button.  I accepted your solution.

thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directiâ€¦
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference texâ€¦
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.