Calculate Top 7 Team Points, and Sort by Scores

[terrykalaka]

Hi All,
Working through more issues converting this google sheets file to excel. I've upgraded to using Excel 2021 program to hopefully be able to more easily see errors in any of the formulas. Background is this is for an amateur bicycle racing series that I'm volunteering to help collect and manage results for. When the file was converted from google to excel, some of the formulas didn't transfer at all, and some are just not working, and some are working and some not for unknown reasons to me.

Currently working on the "Team Standings" tab. The basics of what needs to happen is the top 7 point scores for each team on each race day tab(ELKS, GPCX, BW, DLV etc) get added to the teams total for the season and then the teams are ranked based on their overall score.

The google sheet document had two main operations for this:

One in a series of helper columns(on the right side of the sheet) that collected each unique team name per event using the following formula:
=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX(NASH!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, NASH!$F$2:$F$400)+(NASH!$F$2:$F$400=""), 0)), INDEX(GPCX!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, GPCX!$F$2:$F$400)+(GPCX!$F$2:$F$400=""), 0))), INDEX(ELKS!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, ELKS!$F$2:$F$400)+(ELKS!$F$2:$F$400=""), 0))), INDEX(TBD!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, TBD!$F$2:$F$400)+(TBD!$F$2:$F$400=""), 0))), INDEX(BW!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, BW!$F$2:$F$400)+(BW!$F$2:$F$400=""), 0))), INDEX('ATH1'!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, 'ATH1'!$F$2:$F$400)+('ATH1'!$F$2:$F$400=""), 0))), INDEX('ATH2'!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, 'ATH2'!$F$2:$F$400)+('ATH2'!$F$2:$F$400=""), 0))), INDEX(MACON!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, MACON!$F$2:$F$400)+(MACON!$F$2:$F$400=""), 0))), INDEX(DLV!$F$2:$F$400, MATCH(0, COUNTIF(AB$1:AB1, DLV!$F$2:$F$400)+(DLV!$F$2:$F$400=""), 0))), "")))))))

Collects points from each event using this formula:
=ArrayFormula(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),1),"0")+(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),2),"0"))+(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),3),"0"))+(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),4),"0"))+(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),5),"0"))+(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),6),"0"))+(iferror(Large(IF($AB2=ELKS!$F$1:$F$400,ELKS!$H$1:$H$400),7),"0")))

The final sorting is done on the left side of the sheet using formula:
=Sort(AB2:AC121,2,false)

These formulas don't seem to work in excel, and then I'm wondering if there's a better, completely different pathway to put all this together and get this task completed?

Also looking like I'm having some trouble uploading the spreadsheet file too, unsure what the situation is there?

Thanks in advance

2023 GACX Results Work.xlsx

[AliGW]

The sheet needs to be a SAMPLE sheet - maybe you have not cut it down in size?

Please update your forum profile to Excel 2021.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[terrykalaka]

Hi, thanks AliGW, got the file cut down and uploaded now in the original post

[AliGW]

Great - can you tell me where I shall find the non-working formulae?

[Fluff13]

The formula in AD2 & D2 can be replaced by

Please Login or Register  to view this content.

I cannot find the other formula

[terrykalaka]

The following formula belonged to cell AB2:

Please Login or Register  to view this content.

And this in B2:

Please Login or Register  to view this content.

[Fluff13]

For the other formula try

Please Login or Register  to view this content.

[terrykalaka]

Fluff13, thanks for this.

How can I adapt this formula, if we need to pull unique names from more worksheets? And then can it be set-up to sort based on the calculated team total(column C)?

I attempted this formula to try to adapt for more worksheets:

Please Login or Register  to view this content.

but I'm definitely missing on how the formula is doing all of the operations

[Fluff13]

The formula should be like

Please Login or Register  to view this content.