Results 1 to 8 of 8

Thread: SUM Different Combinations?

  1. #1

    SUM Different Combinations?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    On one page I have 5 columns of names. These are 5 person combinations from a group of 17 people, so there are 6,188 combinations listed.

    On a different sheet, I have these 17 different names listed across the top, and at the end in the column to the right I have a column titled minutes. Each row under the header row (where the 17 names and "Minutes" is labeled) has an "x" in five spots depending on what person was used, and then a number in the minutes column depending on how long they were used for.

    What I want is to be able to Sum the amount of minutes each combination of names was used for on the page where I have listed all the combinations.


    Any help would be greatly appreciated.

  2. #2
    Good morning,

    You could make a concatenate helper field of all the names and then sumifs off a unique list. This will only work properly if the names are sorted the same (example Joe-Bob-Bill-Jane-Sue have to always be in that order, Sue-Jane-Bill-Bob-Joe, while being the same people would be a different combination. If you can post a spreadsheet I can show you what I'm talking about.

    Best of luck,

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    711
    Articles
    0
    Excel Version
    Excel 2010 64bit
    I must be missing something here.

    It appears to me that both sheets would really be the same with the exception of the second sheet including the names that aren't part of the group and therefore wouldn't be part of the combination. Then the second sheet has a minutes column on the end that you want to contain the result of a calculation.

    What I want is to be able to Sum the amount of minutes each combination of names was used for
    What is it that you want to sum? Numbers? From where?

    Sorry, I just don't understand what you want accomplished with what you have presented.

    It would be a good idea to upload an example workbook of what you are working with.

  4. #4
    I have posted the workbook and hopefully this will help. I want to sum the number of minutes that each grouping of 5 people have been together. This is shown by the "x"s on the "Total Raw" tab. The minutes that each of those have been together are also listed on that tab (the minutes haven't been added in for all of the rows that have x's just the first 20 rows or so for now). The output should be the total number of minutes for each combination on the "sheet 2" page.

    Thanks!
    Attached Files Attached Files
    Last edited by rsw34; 2013-10-15 at 08:05 PM. Reason: attachment

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    711
    Articles
    0
    Excel Version
    Excel 2010 64bit
    OK, so based on Total Raw sheet only going down to and including row 24, what should the number be in cell G2 on Sheet 2, and how did you calculate that?

  6. #6
    G2 on Sheet two should have every instance that Ryan,Oliver,Joe,Lonnie, and Dennis have an "x" under their name. A way to think of it is on the Total Raw Sheet each row is a different trial, and I want to get the sum of all the trials when Ryan,Oliver,Joe,Lonnie, and Dennis have an "x". I will eventually have the minutes for a lot more than just 24 trials otherwise I would just manually calculate it. I am looking for a formula starting in G2 on Sheet 2 that I can drag down and apply to all 6,188 combinations.

  7. #7
    Good evening,

    There is probably a cleaner way of doing this, but it will work in a pinch.

    Hope this helps,
    Attached Files Attached Files

  8. #8
    I believe this is what I'm looking for Thanks!

Posting Permissions

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