Data Sorting Question

I've got what I feel like is a pretty basic problem I'm trying to correct with the proper formula.

Here is an image for reference:
ExcelHelp.jpg

All the trucks in Yellow are "Team South" and the trucks in Green are "Team North"

I've got the formula's down to calculate each trucks "Overall Score", example: for Avenger Friday Overall Score it would be =SUM(B9:D9)

However, I want to be able to "sort" the trucks throughout the weekend (so they will be jumbled and not all in order as they are now) but I want the total points for each team (Team South & Team North) to stay the same even after I sort the trucks after each event.

For example, I can't simply use =SUM(E9:12) for the Team South Friday Overall Score, because if I sort and Team North trucks move up into Rows 9-12 it will not keep the proper total points.

So again, how do I make the "Team North" and "Team South" points total proprietary to just the "Team North" and "Team South" trucks, regardless of how I sort them?

Alright I've attached the excel sheet.

Sheet 1 - Begin Sheet, this has everything blank. Team South is Yellow and Team North is Green.

Sheet 2 - Example Points, this would show an example of how the points for a Friday event would work. This also shows what the correct "Team South" points should be (247) and "Team North" points should be (287). The trucks are UNSORTED on this sheet.

Sheet 3 - Trucks Sorted Wrong Team Points, this is the same as Sheet 2, but the trucks are sorted and as you can see, the Team South and Team North points are wrong.

Again, I'm looking to make the Team North points proprietary to the Team North trucks and not have the points change no matter how I sor the trucks. What Excel formula would give me the correct Team points no matter how I sort the trucks?

Not sure if that formula is working FDibbins.

For example, I paste that formula into "E17" (The Overall Score for Team North) and it gives me a total of 79. The point total should be 247 (Avenger 78 + Wrecking Crew 62 + Toxic 53 + Over Bored 54 = 247 Total Points for Team North).

Again for each column, I want the totals for Team North (Avenger, Wrecking Crew, Toxic, & Over Bored all in yellow) on Row 17 to always equal the total for just those 4 trucks, regardless of how I have them sorted.

So Cell B17 should equal whatever total points Avenger, Wrecking Crew, Toxic, and Over Bored have in Column B. In C17 same thing, D17 same thing and so on.

Same thing for all the Team South (Stone Crusher, Hooked, Walking Tall, Saigon Shaker all in green), regardless of how I have them sorted, I want the total points for all 4 of those trucks regardless of how I have them sorted.


Reason being is at this event we are doing an "Individual Champion", as in the truck with the highest points total, and a "Team Champion" which is the team with the highest points total. I would like to always sort the trucks by who has the most points for easier reference, all while keeping the Team Total across the bottom on the same page.

Bump, anyone have any ideas?

Using Ford's formula:
ExcelForumHelp.xlsx