Formula for Sport Rankings

Is this what you were looking for?

See attached.

I agree with MarvinP that separate SUMIFs are the way forward (else use a Multi Consolidation Pivot Table)

You can conduct SUMIF in 3D if you use INDIRECT etc but it's volatile and not particularly efficient IMO

=SUMPRODUCT(SUMIF(INDIRECT("'"&_Sheets&"'!E:E"),B2,INDIRECT("'"&_Sheets&"'!B:B")))

where _Sheets is a Dynamic Named Range defined as:

Name: _Sheets
RefersTo: =OFFSET($G$1,0,0,COUNTA($G:$G),1)

and where sheets to be included in the aggregation (Class A, Class B & Class C) are listed in Column G (G1 onwards)

but to reiterate I concur with MarvinP's approach - the above is merely a demo. of an alternative technique you might otherwise come across.

(Given use of XL2008 UDFs are not viable)

Hi titoesco,

I was wondering if you had all the same school names. I understand the classes as I was a 175 many years ago. Also made it to state.

The answer is all dependent on how you have your data arranged and stored. Pivot Tables could be great as it doesn't need the school or league name to be pre-defined. You could also filter by weight class and/or league or division.

One worksheet per class is not optimal. If you had a single sheet with the weight class as a separate column it would be much better.

Is your original attachment how the data is stored? I think with some easy moving of data around to make a single table a Pivot Table is the answer.

FWIW, attached is a file I put together earlier when answering your thread - perhaps somewhat overlooked (sob)

It demo's both the SUMPRODUCT/INDIRECT approach and also (perhaps more importantly) the Multi Consolidation Pivot.

Regards not knowing what a Pivot Table is - if in doubt research - there's a general intro. link in my sig and here is an overview of a Multi Consolidation Pivot.
(see also: http://www.contextures.com/xlVideos10.html#Multi as referenced on above link)

Hi
Find the attached where I've put all the weight sheets onto a single sheet called All. I have also created an example Pivot Table on this sheet with Weight and Section filters above it. Drop them down and click on them to filter the Pivot. What I think you need it Ranking Points for all the individual records of data. I don't know how you gave points, so I used the ranking numbers instead (which may be meaningless).

See if this helps you understand how the data could be structured and anlyised using Pivot Tables.

Sorry for the delay but football company showed up and I needed to be a host.

Wow! Thank you! This is coming along real nice. Here are the individual tournament points according to the rankings that I am giving:

1. 24
2. 20
3. 16
4. 14
5. 11
6. 9
7. 7
8. 6
9. 4
10. 4
11. 4
12. 4

I'm not sure where to substitute those.

Hi,
This was the part I didn't understand. If 103, Perez was ranked 1 does he get 24 points? I didn't know how assign points based on rankings.

I'll assume that is the case and give points and put that in the Pivot Table and attach it. I just need a little more understanding about points vs rankings.

I've added a second pivot table based on the All data to allow filtering by league/section. You can click the dropdown next to Row Labels in the Pivot table to sort the data from large to small to see who won.

BTW - where is my old school, Pioneer High School in San Jose Unified? Are they part of this? See the Attached with the new Points column and pivot tables.

I hope this helps. If not let me know what else you need.

To make the 11th and 12th ranks equal to no points look at the formula in column I. I just make the 11th and 12th ranks 0 instead of 4. No problem for that one. I've added a count of ranks onto the first pivot. If you want the count to only include ranks above a certain number you will need to click on the Row Label drop down and specify to only include ranks less than 11.

Find the attached with your changes. Good wrestling!