Solved

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

Posted on 2012-04-10
13
475 Views
Last Modified: 2012-04-14
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
Comment
Question by:abgail
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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

by:Shanan212
Comment Utility
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

by:anthonymellorfca
Comment Utility
I'll pick up the pivot table invite using your file dave,
xls is also attached.

Pivot and formula solutions in one
ugh, forgot the sort, back in a minute

Anthony
0
 

Author Comment

by:abgail
Comment Utility
Using the pivot table, is there a way to sort by total?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 9

Expert Comment

by:anthonymellorfca
Comment Utility
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

by:abgail
Comment Utility
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

by:
anthonymellorfca earned 500 total points
Comment Utility
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.Show sort in pivot table Excel 2008
0
 
LVL 9

Expert Comment

by:anthonymellorfca
Comment Utility
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

by:anthonymellorfca
Comment Utility
I think abgail has pressed the wrong button, having done that myself on occasion. Anthony
0
 

Author Comment

by:abgail
Comment Utility
Yes, I hit the wrong button.  I accepted your solution.

thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now