Sum of an IF/AND statement
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Results 1 to 4 of 4

Thread: Sum of an IF/AND statement

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,622
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    1 Thread(s)

    Default 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})
    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum of an IF/AND statement

    Quote Originally Posted by FormR View Post
    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. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,622
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sum of an IF/AND statement

      
    Quote Originally Posted by Amonsul View Post
    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)

    [code]your code[/code]

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com