I'm struggling with a formula to count wins and losses for my wrestlers. I swear I've seen this before, but can't replicate it. I've tried combinations of vlookup and countif, but can't get it to work. I need totals for wins, losses, takedowns, then just simply display the fastest pin time.
Is anyone able to assist me with this? I've attached a redacted spreadsheet, and the totals are needed in the box at the bottom.
EDIT - I think I did the upload correct. Let me know if it doesn't show up.
You are going to need to explain how a pin is calculated - it's not clear on your sheet. You did not include any sample results, manually calculated, so there will be some guesswork here unless you clarify.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are two versions of a win/loss shown. One is a point value, ie "10-5". The other, a 'pin', is displayed in time format, ie "1:19". Column D contains both these versions. There can be an extra column added to make the formula easier if needed.
Fastest pin is simply the lowest time amount. So for example, :31 in row 42 shows the wrestler pinned his opponent in 31 seconds. In row 39, the 3:02 shows that the wrestler pinned his opponent in 3 minutes and 2 seconds. Fastest pin is obviously the lesser amount of time.
Wins:
=COUNTIFS($B$5:$B$47,"Won",$A$5:$A$47,A53)
Losses:
=COUNTIFS($C$5:$C$47,"Lost",$A$5:$A$47,A53)
Takedowns:
=SUMIFS($E$5:$E$47,$A$5:$A$47,A53)
Pins:
No clue - sorry!
Last edited by AliGW; 01-08-2019 at 02:22 PM.
Fastest pin, is bit of an issue as you don't have consistent format in D column. What should be value returned for Wrestler C and Wrestler J for an example?
For wins and losses. Enter in B53: =COUNTIFS($A$5:$A$47,$A53,B$5:B$47,"<>")
Copy down and across.
For take downs, I'm assuming you want SUM.
In E53: =SUMIF($A$5:$A$47,$A53,$E$5:$E$47)
Copy down.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
If all the pins were in a dedicated column, would there be a solution?
Any chance of telling those of us who don't know exactly what a pin is what it is and where to find the data in your table? Were the formulae I gave you of any use at all?
Yes, the formulas you provided worked perfectly. I'm trying to see if the "IFS" function can be applied to pins.
In wrestling, the match lasts 6 minutes. Either the wrestlers go the entire time and one wins by points(10-2 or 12-6, etc), or one wrestler pins the other, and the match ends early(:13). 13 second pin is rare, but it happens.
Yes, that would simplify things. You can then just use Aggregate function, MIN(IF()) array or MINIFS (only for Office 2019, or Office 365 subscriber).
Ex: Assuming Column D holds Pins Time only.
In D53: =AGGREGATE(15,6,($D$5:$D$47)/($A$5:$A$47=$A53),1)
Copy down.
Edit: See attached incorporating 6:00 when won with decision. And "N/A" when no wins recorded.
Last edited by CK76; 01-08-2019 at 02:42 PM.
That works. Unfortunately it also counts the pins for when that wrestler has LOST. Is there a way to use an IF function with the AGGREGATE function so that the fastest pin is only counted when the wrestler won?
See the edit in previous post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
DiligentCoach 
Register To Reply
Bookmarks