Need Help: Adding up the scores

[Jung Bin]

Hi guys,

I have created a system for the sports day, I have included vlookup functions which would allow me to find the house group each student belongs in.
The only problem I am encountering now is adding up the scores, I would like to use some kind of formula which would allow me to add up the scores for each house group.
I have 4 house groups and for each event there will be 3 winners which would be shown automatically once I input the record of each students, I would like to add up the scores separately according to the house group.
I am doing this to show which house group has won/winning on the sports day.
It would make much more sense once you look at the system, The 'summary' page is where I want the scores to be displayed.
If you could think of any ways, please don't hesitate to take a look or leave a message
Thank you guys

p.s. sorry for bad explanations, English is not my first language.

[Ochimus]

Jung Bin,

The easiest solution is to create "Named Ranges" for the results on each page, and then "sumifs, index and match", but before I can do that I need some help with one question:

In rows 6 - 15 there are five races, each with nine runners.

Does each student run only once? Or do the winners in one heat then race against winners in another Heat?
Because in rows 17 - 20 you have "results" under Race 1 only . Will you want a different bloc also in G17 = K20 for Race 2, M17 - Q20 for Race 3, etc?


Ochimus

[JohnTopley]

See the attached:

I have changed the "Summary" to have separate lines for Boys and Girls plus Total.

I recommend you put an error condition round the VLOOKUP to remove #N/A errors

=IFERROR(VLOOKUP(........),"")

General formula used ...

=SUMPRODUCT(--('Year 13 Boys'!$A$6:$A$200="1st")+('Year 13 Boys'!$A$6:$A$200="2nd")+('Year 13 Boys'!$A$6:$A$200="3rd"),--('Year 13 Boys'!$D$6:$D$200=Summary!B4),('Year 13 Boys'!$E$6:$E$200))

Also "Sheet1" as a possible alternative layout.

[Ochimus]

Jung Bin,

Second problem. In the "results" boxes, the winner in a race is the lowest number (because that is the fastest time),

But the formula for anything throwing or jumping needs the winner to be the highest number (because that is the longest distance).

Ochimus

[Jung Bin]

The students will only run once, I have created many heats because I wasn't sure at the time how many students will participate. But now i know there will be less students therefore less races/heat. But the winners are still going to be the top results from those results

[Jung Bin]

Yes, that's true, will that cause any major problems?

[Jung Bin]

See the attached:

I have changed the "Summary" to have separate lines for Boys and Girls plus Total.

I recommend you put an error condition round the VLOOKUP to remove #N/A errors

=IFERROR(VLOOKUP(........),"")

General formula used ...

=SUMPRODUCT(--('Year 13 Boys'!$A$6:$A$200="1st")+('Year 13 Boys'!$A$6:$A$200="2nd")+('Year 13 Boys'!$A$6:$A$200="3rd"),--('Year 13 Boys'!$D$6:$D$200=Summary!B4),('Year 13 Boys'!$E$6:$E$200))

Also "Sheet1" as a possible alternative layout.

Thank you for the information, I have tested out your formula but I don't think it will help me to find the total scores for each 'house group', maybe I've done something wrong but as far as I tested, I was unable to get the scores for each house group. thank you for your attention and efforts though, really appreciate it.

[Jung Bin]

Jung Bin,

The easiest solution is to create "Named Ranges" for the results on each page, and then "sumifs, index and match", but before I can do that I need some help with one question:

In rows 6 - 15 there are five races, each with nine runners.

Does each student run only once? Or do the winners in one heat then race against winners in another Heat?
Because in rows 17 - 20 you have "results" under Race 1 only . Will you want a different bloc also in G17 = K20 for Race 2, M17 - Q20 for Race 3, etc?


Ochimus

Thank you for the reply, so do you think the best way to figure out the scores for each house group is to make a separate table below each sheet to display for that year group and gender, with house groups, and just add up the totals for each sheet for the final score? It sounds like a good idea but I am not very good with nested functions, if you could show me how to do it, it will help me a lot, thank you.

[JohnTopley]

There are results in the Boys sheet and they were put in the summary page. OK. These are in file I posted.

I have just added further results and the numbers have been updated.

if you don't think it works post a file demonstrating the problem.

[JohnTopley]

See attached: manually check Boys vs Summary.

[Jung Bin]

See attached: manually check Boys vs Summary.

Thank you for the reply, I think What you have created works great, but the formula you have used is only the total score for 'Year 13 Boys' and 'Year 13 Girls' right? So in order to display the sum of whole year groups, do I have to change for formula or do you suggest on having scoring on each sheet/year group OR creating another tables for each year group on summary page

[JohnTopley]

Not sure what you mean by "whole year groups".

If you mean all Year 13 (i.e. boys+girls: that is the TOTAL line in the summary.

You only show one year group (13) but if there are others simply replicate the formulae changing the sheet names to 'Year nn Boys'! where nn is the year.

[Jung Bin]

Not sure what you mean by "whole year groups".

If you mean all Year 13 (i.e. boys+girls: that is the TOTAL line in the summary.

You only show one year group (13) but if there are others simply replicate the formulae changing the sheet names to 'Year nn Boys'! where nn is the year.

Yeah I will try replicating formulae and see if I can find the total points for each house group across year 7 to year 13. Thank you

[JohnTopley]

Sample attached

[Ochimus]

Jung,

Attached please find one file fully "worked up" for Years 12 and 13 Males and Females.

Each worksheet has a score in rows 136 - 140 to enable you to check the worksheet "balances", and the combined scores for the four sheet are in the Summary sheet.

As discussed earlier, the results for the jumping and running events report the highest score as the winner, whilst the running events report the lowest score as the winner.

NOTE:
There will be a problem if two pupils record the same score in an event.
E.g. In E7 - E15, Nada Fawzy and Gillian Ann Francis come "joint second" in the race with identical records.

The formula will match only whichever name is the first scoring "2", and will enter it twice in B19 and B20, rather than putting both names as "joint second"., which would also mean having to change the scores in the results box?

Not sure how you want to handle that?

Ochimus

[Jung Bin]

Jung,

Attached please find one file fully "worked up" for Years 12 and 13 Males and Females.

Each worksheet has a score in rows 136 - 140 to enable you to check the worksheet "balances", and the combined scores for the four sheet are in the Summary sheet.

As discussed earlier, the results for the jumping and running events report the highest score as the winner, whilst the running events report the lowest score as the winner.

NOTE:
There will be a problem if two pupils record the same score in an event.
E.g. In E7 - E15, Nada Fawzy and Gillian Ann Francis come "joint second" in the race with identical records.

The formula will match only whichever name is the first scoring "2", and will enter it twice in B19 and B20, rather than putting both names as "joint second"., which would also mean having to change the scores in the results box?

Not sure how you want to handle that?

Ochimus

Thank you Ochimus,

I think what you have created is pretty neat and should work fine. For the joint second, it won't be a major issue since I can write the score down manually for that and add up later, but I highly doubt there will be any cases. For the events that reports highest score as the winner, there won't be joint second place since there will be elimination.
I will try to implement your formulae throughout entire years to see if it works.
Thanks for the help, really appreciate it.

Jung Bin

[Ochimus]

Jung Bin,

Many thanks for the kind words,

If the other years have the same events, you can simply "copy" the Year 13 boys sheet into the file (changing the title and renaming the sheet, of course).

You will then need to add the "new sheet" into the formulae in the Summary sheet. E.g:

='Year 12 Girls'!C136+'Year 12 Boys'!C136+'Year 13 Girls'!C136+'Year 13 Boys'!C136
becomes
='Year 11 Girls'!C136+'Year 11 Boys'!C136+'Year 12 Girls'!C136+'Year 12 Boys'!C136+'Year 13 Girls'!C136+'Year 13 Boys'!C136

Ochimus

[Ochimus]

Jung Bin,

Attached has years 7 - 13 "filled in" for both Boys and Girls.

The formula in the Summary sheet showing the correct total scores for all four houses across the fourteen worksheets.

To enable you to check the number, I have added a "checksum" bloc to the summary sheet, which links to the scores for each house in each worksheet, and confirms the scores add up to the totals in rows 5 and 6.

Most sheets have "identical" scores because I "copied and pasted" Year 12 for the Girl sheets and Year 11 for the Boys, but "proved" the format by changing Year 7 for the Girls.

If you clear the names in the sheets, and add your own times for the events, the scores will change automatically,

Ochimus

[Ochimus]

Not sure what happened there?

File should be attached now.

Ochimus