Hi, welcome to the forum!
Here is one option that you can try in I2
=SUMPRODUCT(COUNTIFS(E2:E100,{"W","L"},F2:F100,{"Pin";"<>Pin"}),{4,-0.5;2,-0.5})
I am having a problem with my formula. I have a wrestling record book that I am trying to get points for. Example if a person won a match with a pin I want them to get 4 points. If they Lose a match they lose .5 points. I want to be able to add up points for Wins/Loses and How they won. The problem is the If/and statements do not add up the points.
I get the 3.5 for the Win/Loss but it doesn't add the the 4 points for the Pins. Can someone help?
Weight Wrestler Opponent School Result How Record Pins Points OP1 W Pin 2-1 2 Op2 W Pin OP3 L Dec
So my code in I2 is {=SUM(IF((E2:E100="W"),2,0),IF((E2:E100="L"),-0.5,0),IF(AND(E2:E100="W",F2:F100="Pin"),2,0),IF(AND(E2:E100="W",F2:F100="TF"),1,0))}
Hi, welcome to the forum!
Here is one option that you can try in I2
=SUMPRODUCT(COUNTIFS(E2:E100,{"W","L"},F2:F100,{"Pin";"<>Pin"}),{4,-0.5;2,-0.5})
[code]your code[/code]
Thank you that worked great! I am not quite sure what is going on here. How would I expand it? I also need to add in things like a Win in Result but a Dec in How. The Win would be worth 2 points and the Dec would be worth 1 point. So in the example below should have the points of 10.5.
6 points for W
4 points for Pin
1 points for Dec
-.5 points for L
Weight Wrestler Opponent School Result How Record Pins Points OP1 W Pin 2-1 2 Op2 W Pin OP3
OP4L
WDec
Dec
Hi, I don't understand your latest description of the points, but here is a suggestion that gives you a lot of flexibility and maintainability.
Here you would make a table of all the possible outcomes (cells K2:M5 in this example) and assign the points you want to award for each outcome and use as below.
Excel 2013/2016
E F G H I J K L M 1 Result How Record Pins Points Result How Points 2 W Pin 42737 2 15.5 W Pin 6 3 W Pin W Dec 4 4 L Dec L Pin -0.5 5 L Dec -0.5 6 W Dec Sheet1
Worksheet Formulas
Cell Formula I2 =SUMPRODUCT(COUNTIFS(E:E,K2:K5,F:F,L2:L5),M2:M5)
[code]your code[/code]
Like this thread? Share it with others