Excluding ranges from arrays

Hi.

I am creating a competiton spreadsheet that will rank groups overall (they are listed by division) and breaks any ties with the value in the C column. There are 12 groups in each division and an exhibition group. They are placed in an overall ranking format. The top group receives a special award (G82) and the next ten after that are ranked 1st through 10th. (listed at the bottom of the sheet)
The score from the exhibition group should not count in the overall ranking so I have excluded # 16,29,42,55, and 68 from the ranges. See formula:

=IF(G4="","",RANK(G4,($G$4:$G$15,$G$17:$G$28,$G$30:$G$41,$G$43:$G$54,$G$56:$G$67,$G$69:$G$80),0)+SUMPRODUCT(--($G$4:$G$80=G4),--($C$4:$C$80>C4))-COUNTIFS($G$4:$G$80,LEFT($G$82,FIND(" ",$G$82))+0,$B$4:$B$80,MID($G$82,FIND(" ",$G$82)+1,255)))

The problem occurs when an exhibition groups ties another group for 1st, then the SUMPRODUCT arrays get screwed up and it starts ranking from 2 and causing a #N/A in the 1st place listing.

Any ideas how to exclude the exhibition groups from the array? I am attaching a sample sheet.

Thanks

Hi John,

In golf we do a scorecard playoff tiebreaker. That uses the scores on the last holes to determine the tiebreaker. I suggest you use the Excellence number and add it divided by 1000 to the Net scores to reduce possible ties.

Does that work for you?

To solve your specific issue here you can add another "clause" to the SUMPRODUCT formula, i.e.

=IF(G4="","",RANK(G4,($G$4:$G$15,$G$17:$G$28,$G$30:$G$41,$G$43:$G$54,$G$56:$G$67,$G$69:$G$80),0)+ SUMPRODUCT(--($G$4:$G$80=G4),--($C$4:$C$80>C4),--($J$4:$J$80<>"Exh"))-COUNTIFS($G$4:$G$80,LEFT($G$82,FIND(" ",$G$82))+0,$B$4:$B$80,MID($G$82,FIND(" ",$G$82)+1,255)))

..but I'd be inclined to re-write a little - you can simplify and streamline so that you can use this formula for the same results

=IF(G4="","",IF(J4="Exh","Exh",COUNTIFS(G$4:G$80,">"&G4,J$4:J$80,"<>Exh")+1+COUNTIFS(G$4:G$80,G4 ,C$4:C$80,">"&C4,J$4:J$80,"<>Exh")-SUMPRODUCT((G$4:G$80&" "&B$4:B$80=G$82)+0)))

You can put that in I8 and copy all the way down, even through the Exh rows......