Sum of an IF/AND statement

[Amonsul]

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?

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Weight[/TD]
[TD]Wrestler[/TD]
[TD]Opponent[/TD]
[TD]School[/TD]
[TD]Result[/TD]
[TD]How[/TD]
[TD]Record[/TD]
[TD]Pins[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OP1[/TD]
[TD][/TD]
[TD]W[/TD]
[TD]Pin[/TD]
[TD]2-1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Op2[/TD]
[TD][/TD]
[TD]W[/TD]
[TD]Pin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OP3[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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))}

 

[FormR]

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})

 

[Amonsul]

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})

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

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Weight[/TD]
[TD]Wrestler[/TD]
[TD]Opponent[/TD]
[TD]School[/TD]
[TD]Result[/TD]
[TD]How[/TD]
[TD]Record[/TD]
[TD]Pins[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OP1[/TD]
[TD][/TD]
[TD]W[/TD]
[TD]Pin[/TD]
[TD]2-1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Op2[/TD]
[TD][/TD]
[TD]W[/TD]
[TD]Pin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OP3

OP4[/TD]
[TD][/TD]
[TD]L

W[/TD]
[TD]Dec

Dec[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

I also need to add in things like a Win in Result but a Dec in How.

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

Cell Formulas
Range	Formula
I2	=SUMPRODUCT(COUNTIFS(E:E,K2:K5,F:F,L2:L5),M2:M5)