+ Reply to Thread
Results 1 to 7 of 7

Sumproduct with if function??

  1. #1
    Registered User
    Join Date
    12-03-2015
    Location
    sidney, ne
    MS-Off Ver
    Office 2013
    Posts
    29

    Sumproduct with if function??

    TEAM Ave Points Home Ave Road Ave Ave Allow Home Allow Road Allow Home Away AVE PTS OFF PTS DEF PTS O/U
    Boston 102.82 103.42 102.22 98.31 98.54 98.09 Brooklyn Boston
    Brooklyn 105.42 104.88 105.96 108.79 107.04 110.54
    New York 105.12 107.54 102.69 106.03 102.71 109.35


    Call the Team Column Colum A and the O/U Column M. The Home column(H) and the Away column(I) will always have a Team from column(A) in those columns. I need the ave pts column(J) to add the Ave Points Column(B) that are tied to the team name in the Team column(A).

    Also, for column OFF PTS Column(K) I need it to add the column Home Ave(C) for the Team in the Home Column(H)(which matches a team name in the Team Column(A) with the Road Ave Column(D) for the Team in the Away Column(I)(which matches a team name in the Team Column(A)

    My goal is to be able to mix and match any home and away team in columns H and I, and have the ave pts / off pts / def pts populate into those columns based off of their averages in B, C, D, E, F, and G.

    Examples - Ave Pts column(J2) needs to add the Home team Column(H)(which will always come from the Team Column(A) with what that teams ave points is in column(B) with what the Away team Column(I)(which will always come from the Team Column(A) with what that teams ave points is in column(B).

    This is very complicated to me, and I'm sure it is confusing, so let me know if you need clarification on anything. I'm a noob and don't know much. Hope this is possible.

    Thank you for any and all help!

  2. #2
    Registered User
    Join Date
    12-03-2015
    Location
    sidney, ne
    MS-Off Ver
    Office 2013
    Posts
    29

    Re: Sumproduct with if function??

    Well sorry that didn't come through like I wanted it to. is there anyway to attach an excel file?

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Sumproduct with if function??

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  4. #4
    Registered User
    Join Date
    12-03-2015
    Location
    sidney, ne
    MS-Off Ver
    Office 2013
    Posts
    29

    Re: Sumproduct with if function??

    Hopefully this worked.
    Attached Files Attached Files

  5. #5
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Sumproduct with if function??

    Hi, to all!

    Try this:
    [K2] : =SUMPRODUCT(SUMIF(A$2:A$4,I2:J2,B$2:B$4))
    [L2] : =SUMIF($A2:$A4,I2,C$2:C$4)+SUMIF($A2:$A4,J2,D$2:D$4)

    And drag it down. Blessings!

  6. #6
    Registered User
    Join Date
    12-03-2015
    Location
    sidney, ne
    MS-Off Ver
    Office 2013
    Posts
    29

    Re: Sumproduct with if function??

    That works! Thanks!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Sumproduct with if function??

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUMPRODUCT function?
    By adrienmeszaros in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2015, 02:59 AM
  2. SUMPRODUCT and OR function
    By ebeyert in forum Excel General
    Replies: 2
    Last Post: 08-21-2014, 03:06 AM
  3. [SOLVED] Sumproduct Function with Other Function References
    By T86157 in forum Outlook Formatting & Functions
    Replies: 4
    Last Post: 07-30-2012, 04:56 PM
  4. Replies: 9
    Last Post: 07-02-2012, 07:02 PM
  5. Replies: 10
    Last Post: 11-11-2010, 03:49 PM
  6. Help with Sumproduct function
    By Jazmania in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-10-2010, 03:12 PM
  7. SUMIF Function Inside SUMPRODUCT Function
    By Abdul Waheed in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-19-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1