Mesh joiner
New Member
- Joined
- Jan 26, 2014
- Messages
- 2
I have successfully created double tie breakers using the sumproduct function for ranking in columns AI-AS. They were working perfectly as long as every row was filled. However, realistically, not every row will be filled (some blank rows will be hidden). I think I need to add a countif function, but i can't figure out where to put it. HELP!
| tiebreaker countif.xlsx | |||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | |||
| 1 | Beauty | OOC | |||||||||||||||||||||||||||||||||||||||||||||
| 2 | Judge 1 | Judge 2 | Judge 3 | Judge 1 | Judge 2 | Judge 3 | |||||||||||||||||||||||||||||||||||||||||
| 3 | Contestant Number | Contestant Name | Group | Division | First Impression | Facial Beauty | Stage Presence & Poise | Dress | First Impression | Facial Beauty | Stage Presence & Poise | Dress | First Impression | Facial Beauty | Stage Presence & Poise | Dress | Overall Appearance | Personality & Projection | Appeal of Themewear | Overall Appearance | Personality & Projection | Appeal of Themewear | Overall Appearance | Personality & Projection | Appeal of Themewear | Total Beauty | Beauty Tie Breaker 1 | Beauty Tie Breaker 2 | Total OOC | OOC Tie Breaker 1 | OOC Tie Breaker 2 | Total Score | Total Tie Breaker 1 | Total Tie Breaker 2 | Grand Rank | Division Rank | Beauty Rank | OOC Rank | DO Dress | DO Dress Rank | DO Facial Beauty | DO Personality | Age Group OOC | Age Group Beauty | Age Group Rank | ||
| 4 | 26 | Z | Precious A | Div 1 | 9.7 | 9.8 | 9.5 | 9.8 | 9.5 | 9.5 | 9.8 | 9.9 | 9.8 | 9.5 | 9.7 | 9.8 | 9.9 | 10 | 9.5 | 9.8 | 9.6 | 10 | 9.6 | 9.9 | 9.8 | 86.8 | 28.8 | 29 | 88.1 | 29.3 | 29.5 | 174.9 | 86.8 | 28.8 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||
| 5 | 27 | AA | Precious A | Div 1 | 10 | 9.9 | 9.7 | 9.8 | 9.7 | 9.9 | 9.5 | 9.9 | 9.8 | 10 | 9.7 | 10 | 9.8 | 10 | 9.8 | 10 | 9.9 | 9.7 | 9.8 | 9.6 | 9.8 | 88.2 | 29.8 | 28.9 | 88.4 | 29.6 | 29.5 | 176.6 | 88.2 | 29.8 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||
| 6 | 28 | BB | Precious A | Div 1 | 9.5 | 9.8 | 10 | 9.8 | 9.7 | 9.5 | 9.6 | 9.8 | 9.8 | 9.9 | 9.8 | 9.5 | 9.9 | 9.8 | 9.9 | 9.8 | 9.9 | 10 | 9.8 | 9.9 | 9.8 | 87.6 | 29.2 | 29.4 | 88.8 | 29.5 | 29.6 | 176.4 | 87.6 | 29.2 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||
| 7 | 29 | CC | Precious A | Div 1 | 9.5 | 9.9 | 9.5 | 9.8 | 9.9 | 9.5 | 9.5 | 9.5 | 9.7 | 9.5 | 10 | 9.7 | 10 | 9.7 | 9.7 | 9.7 | 10 | 9.6 | 9.7 | 9.8 | 9.9 | 87 | 28.9 | 29 | 88.1 | 29.4 | 29.5 | 175.1 | 87 | 28.9 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||
| 8 | 30 | DD | Precious A | Div 1 | 10 | 9.7 | 10 | 10 | 9.6 | 9.8 | 10 | 10 | 9.9 | 9.9 | 9.6 | 9.6 | 9.5 | 9.7 | 9.6 | 9.5 | 9.6 | 9.5 | 9.6 | 10 | 9.8 | 88.5 | 29.4 | 29.6 | 86.8 | 28.6 | 29.3 | 175.3 | 88.5 | 29.4 | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||
PM Required | |||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Z4:Z8 | Z4 | =IF(A4="","",SUM(E4:G4,I4:K4,M4:O4)) |
| AA4:AA8 | AA4 | =IF(A4="","",SUM(F4,J4,N4)) |
| AB4:AB8 | AB4 | =IF(A4="","",SUM(G4,K4,O4)) |
| AC4:AC8 | AC4 | =IF(A4="","",SUM(Q4:Y4)) |
| AD4:AD8 | AD4 | =IF(A4="","",SUM(Q4,T4,W4)) |
| AE4:AE8 | AE4 | =IF(A4="","",SUM(R4,U4,X4)) |
| AF4:AF8 | AF4 | =IF(A4="","",Z4+AC4) |
| AG4:AG8 | AG4 | =IF(A4="","",Z4) |
| AH4:AH8 | AH4 | =AA4 |
| AI4 | AI4 | =IF(A4<>"",SUMPRODUCT(--(($AF$4:$AF$104+$Z$4:$Z$104*10^-3+$AA$4:$AA$104*10^-6)>AF4+Z4*10^-3+AA4*10^-6))+1,"") |
| AJ4:AJ8 | AJ4 | =IF(OR(A4="",AI4=1),"",SUMPRODUCT(--(($AF$4:$AF$43+$Z$4:$Z$43*10^-3+$AA$4:$AA$43*10^-6)>AF4+Z4*10^-3+AA4*10^-6))+1) |
| AK4:AK8 | AK4 | =IF(OR(A4="",AI4=1,AJ4=1),"",SUMPRODUCT(--(($Z$4:$Z$123+$AA$4:$AA$123*10^-3+$AB$4:$AB$123*10^-6)>Z4+AA4*10^-3+AB4*10^-6))+1) |
| AL4:AL8 | AL4 | =IF(OR(A4="",AI4=1,AJ4=1,AK4=1),"",SUMPRODUCT(--(($AC$4:$AC$123+$AD$4:$AD$123*10^-3+$AE$4:$AE$123*10^-6)>AC4+AD4*10^-3+AE4*10^-6))+1) |
| AM4:AM8 | AM4 | =IF('[Audit Book Woorking TEST.xlsx]Roster'!G2="Y",SUM(H4,L4,P4),"") |
| AN4:AN8 | AN4 | =IF(AM4="","",RANK(AM4,AM:AM)) |
| AO4:AO8 | AO4 | =IF('[Audit Book Woorking TEST.xlsx]Roster'!I2="Y",RANK(AA4,AA:AA),"") |
| AP4:AP8 | AP4 | =IF('[Audit Book Woorking TEST.xlsx]Roster'!H2="Y",RANK(AD4,AD:AD),"") |
| AQ4:AQ8 | AQ4 | =IF(OR(A4="",AL4=1),"",SUMPRODUCT(--(($AC$4:$AC$13+$AD$4:$AD$13*10^-3+$AE$4:$AE$13*10^-6)>AC4+AD4*10^-3+AE4*10^-6))+1) |
| AR4:AR8 | AR4 | =IF(OR(A4="",AK4=1),"",SUMPRODUCT(--(($Z$4:$Z$13+$AA$4:$AA$13*10^-3+$AB$4:$AB$13*10^-6)>Z4+AA4*10^-3+AB4*10^-6))+1) |
| AS4,AS6:AS8 | AS4 | =IF(OR(AI4=1,AJ4=1,AK4=1,AL4=1),"",SUMPRODUCT(--(($AF$4:$AF$13+$Z$4:$Z$13*10^-3+$AA$4:$AA$13*10^-6)>AF4+Z4*10^-3+AA4*10^-6))+1) |
| AI5:AI8 | AI5 | =IF(A5="","",SUMPRODUCT(--(($AF$4:$AF$104+$Z$4:$Z$104*10^-3+$AA$4:$AA$104*10^-6)>AF5+Z5*10^-3+AA5*10^-6))+1) |
| AS5 | AS5 | =IF(OR(AI4=1,AJ4=1,AK4=1,AL4=1),"",SUMPRODUCT(--(($AF$4:$AF$13+$Z$4:$Z$13*10^-3+$AA$4:$AA$13*10^-6)>AF5+Z5*10^-3+AA5*10^-6))+1) |
