Needing a forumal that will calculate a winner

[kansfan2001]

Howdy and thanks for the help

Here's what I have:

I have a spreadsheet that is pulling all the information from multiple locations. What is happening is that I have a macro "Add Picks" button that goes to the forumla sheet and copies everything and paste just the values here shown above.

From row 16 down has no formulas everything is being adjusted thru conditional formatting Except column Q. Which is where my question comes in to play.

I am simply looking for a formula to be able to add up the total number of match wins 1-12. Then if we have a tie on wins it needs to look at whos the closest to the Tie-Breaker to determine the victor. If still tied then show as a tie. Here's the formula I have in column Q but its not right

=IF($C$9="",0,IF($C16=$C$9,1,0))+IF($D$9="",0,IF($D16=$D$9,1,0))+IF($E$9="",0,IF($E16=$E$9,1,0))+IF($F$9="",0,IF($F16=$F$9,1,0))+IF($G$9="",0,IF($G16=$G$9,1,0))+IF($H$9="",0,IF($H16=$H$9,1,0))+IF($I$9="",0,IF($I16=$I$9,1,0))+IF($J$9="",0,IF($J16=$J$9,1,0))+IF($K$9="",0,IF($K16=$K$9,1,0))+IF($L$9="",0,IF($L16=$L$9,1,0))+IF($M$9="",0,IF($M16=$M$9,1,0))+IF($N$9="",0,IF($N16=$N$9,1,0))+IF($O$9="",0,IF($O16=$O$9,1,0))

Also If I could make rows 16 and below adjust to show the place of order that would be epic as well..... but ill just take a formula that I can put in row q to determine who wins

Thanks again for all the help.

 

[kansfan2001]

Typing to fast Formula.... sorry... not sure why you cant fix a post heading?

 

[C Moore]

This will replace the formula in Q16 then fill down.

=SUMPRODUCT((C16:N16=$C$9:$N$9)+0)+IF(O16=$O$9,0.1,0)

Use MAX to find winner.

 

[kansfan2001]

Thanks a bunch

The code works just fine. I added an If statement at the beginning to not show a zero when nothing is in column b for that row. But it when it calculates it is also picking up match 11 and 12 which has no fights its just counting as a win for everyone. Do you know how I can fix this to check to see if the match as a fight listed before it adds to the win?

=IF(B16="","",SUMPRODUCT((C16:N16=$C$9:$N$9)+0)+IF(O16=$O$9,0.1,0))

 

[C Moore]

quickest way would be to throw TBD in the results table