formula for highest score, determined by sum of scores

[patehi]

Hi guys!

Im hoping you could help me on this matter. Its kinda hard for me to explain it, so i will use students as an example for my case. So lets say i have a list of students, and their scores for each subject. I want to get a formula for the top student, second best, third best student and so on. The top student is the one who gets the most scores (sum of all subjects). Now what is tricky, is that i want the "highest score of the subject" that the top students get.

As an example, Top 1 Anna have sum scores of 170 for 3 subjects and her best score is 72 in Math. So i would need the formula for this "72".
Top2 Student Tom has a sum score of 139, and his best score is 80 in Math. TOp 3 Student has a sum score of 112, and his best score is 48 in History.

Here is an example of what my data looks like;

Student Subject Score Sum of score
Anna Math 72 170
Anna Physics 56 170
Anna History 42 170
Tom Math 80 139
Tom English 27 139
Tom Chemistry 32 139
Jan Physics 25 112
Jan History 48 112
Jan English 39 112

I would really appreciate it if someone can help me find the formula for this. For the sum i use SUMIF. I also tried LARGE and i couldnt get it right, since i have an extra column for SUM of data, i always end up with Anna as a second highest score(duplicates of Sum scores)

i was hoping for a formula that i could put on any cell (example on a different sheet) where i already made a layout and space for this. So dragging wouldnt probably work because the cells are placed randomly and far away from each other.

I have a huge list of data, and this data changes all the time. So i just wanted to leave the formula in the cell, so when the data changes, it automatically updates the scores.

Also, on the extra sheet i wouldnt need the 1,2,3 Ranks. I just need the scores, example something like this:

Class 1
Anna -----Tom -------Jan
72 Math --80 Math --48 History

And my list changes all the time, sometimes i have 100 lists of names, sometimes 200.so i need a formula that use the whole column formula (A:A) instead of specific columns and tables.

i hope i made that understandable. English is not my first language.
So if anyone could help me on this matter, i apreciate it greatly!

[Fotis1991]

An Array Large(if... should be what you need. Can you upload a sample workbook?

[patehi]

Hi Fotis!

Thanks for stopping by,
how can i upload a sample workbook?

[satputenandkumar0]

Click on Go Advanced below this thread and then on 'Manage Attachments'.

[patehi]

Here is a sample workbook.

You can see on the first sheet i have a layout and empty cells where i put formulas.
Of course, the yellow areas are the one that need formula, and so far i have a formula to get the highest person's sum of score, which is Anna. The second one is Tom, but the third one is also TOm which is wrong.

I also need the formula to get the score as follow
72 80 48.

if i had the formula, it is possible to use index so i could return and get the name or any other information from the table.

I hope the sample book is okay.

[Fotis1991]

Take a look to the example sheet.

Be carefull with the formulas, because some of these are different from table to table.

[patehi]

Take a look to the example sheet.

Be carefull with the formulas, because some of these are different from table to table.

I have one question, do i have to update the formula each time i have new list?
because it seems the row and colum in your formula are fixed.
Is there a way i could leave the formula be, like instead of using $A$2:$A$10, i just use A:A?

is there a way to modify it?

Anyway, i wanna thank you Fotis, your awesome!

[Fotis1991]

Yes it works using whole range(example A:A), but my suggestion is to don't use whole column range because your sheet become slower.

[patehi]

Could you give me an example if i wanna use the whole column? because i tried just now and it didnt work >.<
you dont have to do the whole formulas, maybe just one, i would appreciate it!

[Fotis1991]

Probably you don't confirm most of the foemulas as Array(control+shift+enter-not just enter)

Read here about these formulas.

http://www.cpearson.com/excel/ArrayFormulas.aspx

[patehi]

Probably you don't confirm most of the foemulas as Array(control+shift+enter-not just enter)

Read here about these formulas.

http://www.cpearson.com/excel/ArrayFormulas.aspx

Oh wow thank you Fotis! Youre really my life saver!
Im a beginner with excel and i need to sort out a huge data list by tomorrow.
I guess ive heard about ctr+shift+enter alternative, but i never know how or when to use it.
Thanks again Fotis for your help!!

[Fotis1991]

You are welcome my friend. Glad that i was helpfull for you!