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?

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

[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

Weight	Wrestler	Opponent	School	Result	How	Record	Pins	Points
		OP1		W	Pin	2-1	2
		Op2		W	Pin
		OP3 OP4		L W	Dec Dec

[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

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