+ Reply to Thread
Results 1 to 5 of 5

Sum valves from different columns considering multiple variables

  1. #1
    Registered User
    Join Date
    12-21-2020
    Location
    Birmingham, England
    MS-Off Ver
    2013
    Posts
    2

    Sum valves from different columns considering multiple variables

    Hi,

    This is my first post on a forum so hoping I've done it all correctly.

    I have my data set out in a specific order which is making this more complicated for me but not something I can change.

    The result I'm looking for is simply to sum up the values for and against a team for the previous 2 events, however, the difficultly I have is that the column the data I need is in varies depending on whether each game is a home/ away game, and so may the lookup variable depending on how you try and solve it.

    I've created a simple and basic unique reference for line of data in the first column to try and aid the process.

    I've highlighted and attached an example which I hope explains things better.

    I've tried long formulas combining if, sum, sumif, sumifs, row, match, index, and large to name a few but have ran out of inspiration on how to make this work.

    I'm running excel 2013.

    Thanks very much for your time, help, and input.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sum valves from different columns considering multiple variables

    I think I follow you...

    use variants of this one:

    =IFERROR(SUM(MOD(AGGREGATE(14,6,(ROW($E2:$F$3)*10^7+$E2:$F$3)/($C2:$D$3=$C3),ROW($1:$2)),10^7)),"")

    see sheet.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sum valves from different columns considering multiple variables

    I've just spotted an error in the fourth result column. It should read:

    =IFERROR(SUM(MOD(AGGREGATE(14,6,(ROW($E2:$F$3)*10^7+$E2:$F$3)/(($C2:$C$3=$D3)+($D2:$D$3=$D3)),ROW($1:$4)),10^7))-I3,"")

    see revised file!!

    I was playing with it and a value of 99999999.9 appearred from nowhere. Suddenly I realised I'd goofed...
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-21-2020
    Location
    Birmingham, England
    MS-Off Ver
    2013
    Posts
    2

    Re: Sum valves from different columns considering multiple variables

    Thank you so much Glenn, couldn't be happier to have a solution to this and so quickly, its been driving me mad.

    Even more happy seeing as I manually added up incorrectly on the example!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sum valves from different columns considering multiple variables

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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

+ 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. Adding columns with multiple variables
    By 1Stacy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-06-2019, 01:12 PM
  2. [SOLVED] Test multiple variables in different columns
    By I.am.Rustam in forum Excel General
    Replies: 4
    Last Post: 10-10-2016, 09:36 PM
  3. [SOLVED] Conditional SUMIF based on multiple columns and multiple row variables
    By jaymaan74 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2016, 11:05 AM
  4. [SOLVED] Sum multiple columns based on multiple variables
    By dmschave in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-29-2014, 11:25 AM
  5. Comparing two columns with multiple variables
    By Tkombz in forum Excel General
    Replies: 2
    Last Post: 04-09-2013, 12:35 AM
  6. Compare multiple columns with multiple variables
    By rfisc270 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-15-2010, 10:40 AM
  7. Need to test for multiple variables in columns
    By donoisbono in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-31-2007, 03:18 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