# Needing a forumal that will calculate a winner

#### kansfan2001

##### New Member
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

VBA Code:
``=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.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### kansfan2001

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

#### C Moore

##### Well-known Member
This will replace the formula in Q16 then fill down.
Excel Formula:
``=SUMPRODUCT((C16:N16=\$C\$9:\$N\$9)+0)+IF(O16=\$O\$9,0.1,0)``
Use MAX to find winner.

#### kansfan2001

##### New Member
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?

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

#### C Moore

##### Well-known Member
quickest way would be to throw TBD in the results table

Replies
5
Views
82
Replies
3
Views
79
Replies
0
Views
75
Replies
1
Views
43
Replies
0
Views
42