Needing a forumal that will calculate a winner

kansfan2001

New Member
Joined
Aug 22, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Howdy and thanks for the help :)

Here's what I have:
1599170810604.png


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.
 

Some videos you may like

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
Joined
Aug 22, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Typing to fast Formula.... sorry... not sure why you cant fix a post heading?
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
540
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 22, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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))

1599287110960.png
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
540
Office Version
  1. 365
Platform
  1. Windows
quickest way would be to throw TBD in the results table
 

Watch MrExcel Video

Forum statistics

Threads
1,114,260
Messages
5,546,827
Members
410,758
Latest member
Bufnercash
Top