I need help creating a formula to rank teams within their league based on specific criteria. Here’s what I’m trying to achieve:
Step 1: Identify all teams from the same league (column A) that have played the same number of matches (values in columns F-G match those of the current row).
Step 2: From the filtered teams, retrieve their scores (columns D-E) to determine their ranking.
For instance, I have a team named “Wolves United” in the “Premier Division” that has played 4 matches and earned 5 points. I want the formula to:
Search for other Premier Division teams that have also played exactly 4 matches.
Compare their points to rank Wolves United among those teams.
Output the rank (like 3rd place) within that group.
The challenging part is that each row needs to calculate its own ranking based on its specific league and match count. I’m considering using the RANK function with some filtering, but I’m unsure how to implement it with multiple criteria.
Can this be done with a single formula applicable to the entire column? Perhaps using ARRAYFORMULA or MAP functions?
The RANK approach should work, but you’ll hit problems when teams have identical points. I’ve wrestled with similar ranking issues and found that mixing RANK with COUNTIFS gives you way better control over ties. Try =COUNTIFS(A:A,A2,F:F,F2,G:G,G2,D:D,">"&D2)+1 - it counts how many teams in the same league with matching games have more points than the current team, then adds 1 for the rank. Handles ties much better than RANK. Need it across multiple rows? Wrap it in ARRAYFORMULA. Just watch out for blank rows screwing up your count logic.
Hit the same issue last year with tournament brackets. Use FILTER with RANK but handle the array right. Try =RANK(D2,FILTER(D:D,(A:A=A2)*(F:F+G:G=F2+G2)),0) - assuming F and G are wins/losses or home/away that you’re adding up. FILTER creates your exact subset before RANK runs on it. Getting errors with blanks? Wrap it in IFERROR. ARRAYFORMULA works for the whole column but test on a small range first - it’ll bog down your sheet with big datasets. Double-check your match counting too - draws and postponed games screw up the count.
try =RANK(D2,FILTER(D:D,(A:A=A2)*(F:F=F2)*(G:G=G2)),0) - this’ll rank points within the same league and match count. you might need to tweak the column refs for your setup, but the logic works.