Sum of an IF/AND statement

Thanks:  0

# Thread: Sum of an IF/AND statement

1. ## Sum of an IF/AND statement

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

2. ## Re: Sum of an IF/AND statement

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

3. ## Re: Sum of an IF/AND statement

Originally Posted by 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})
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

4. ## Re: Sum of an IF/AND statement

Originally Posted by Amonsul
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
EFGHIJKLM
1ResultHowRecordPinsPointsResultHowPoints
2WPin42737215.5WPin6
3WPinWDec4
4LDecLPin-0.5
5LDec-0.5
6WDec

Sheet1

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•