Sorting without affecting sum, average or totals

[thursday140]

I have 5 students in class 100 each one has different grades, and there are 5 more students in class 200 with different grades and 5 more students in class 300 with different grades and it keeps going etc... For class 100 i created a snapshot that shows me their average and sum and class 200 I did the same. Then an average and total for all classes. What I would like to do is sort all the students by name or even by grade without affecting the averages, sum, totals. I've attached a sample sheet. Please help thank you!

[Ace_XL]

Use Sumif and AVERAGEIF

hence

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

You could then sort the data without worrying about the sums and average being out of whack!

[Ace_XL]

Use Sumif and AVERAGEIF

hence

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

You could then sort the data without worrying about the sums and average being out of whack!

[benishiryo]

hi thursday140. try this in F2:
=SUMPRODUCT((LEFT($C$2:$C$11,FIND("-",$C$2:$C$11)-1)=MID($E$1,FIND(" ",$E$1)+1,LEN($E$1)))*($B$2:$B$11))

and this in F3:

Please Login or Register  to view this content.

Copy these formulas to Class 200 & change the cell reference from E1 to E5. a better way is to put the class beside the Sum & Average so that you don't have to keep changing E1

[thursday140]

Ace XL thank you for your quick response, awesome it works!

[thursday140]

Benishiryo, apologies but i was having a tough time using your formula, I'm kinda new to excel. IF you have time maybe you can elaborate more on your response, otherwise Thank you for your time and response you're awesome!

[benishiryo]

using? or understanding? i was actually assuming your classes would have different variations like "100-12", "100-13", etc. actually, i could have used wildcards with SUMIF & AVERAGEIF if there was such a scenario. but AVERAGEIF is only available in Excel 2007 & above, so i generally like to use SUMPRODUCT. i also wanted to make the formula work with your header, so i did all the complicated formulas.

you can see my original formulas i originally gave you in F2:F3. then i had to change the formula slightly in F6:F7 to read "Class 200". i recommended you to put the classes at the side like how i did in column H. then formulas in I2:I3 can be copied to I6:I7 without changing formulas.

the use of wildcards is in column J. it's like Ace_XL's formula, but using the text in column H & extracting the class number "100". i then combined with a wildcard "*". that means to find all cells that start with 100. again, this is for convenience so that you don't have to keep changing formulas.

lastly in column L, you can see that the formula bar shows "100", but it shows "Class 100" in the cell. i showed a printscreen of how i did it through Format Cells. this will then make it even easier for your formulas in column M. it is like column J, without the hassle of using the MID formula

[thursday140]

Different example: Sample sheet attached
I have different product numbers, I want to sum the total amount of products then show delivered and total remaining by product id# the first 6 digits will always remain the same but the last four will be different then the following month the first 6 digits will be different etc... So I would like to sort without it affecting the totals. The only formula I know would be at good use for this one is the, counta. Please advise thank you for your patience any questions please let me know.