+ Reply to Thread
Results 1 to 8 of 8

Finding the max sum value out of multiple "groups"

  1. #1
    Registered User
    Join Date
    10-29-2019
    Location
    London, England
    MS-Off Ver
    365 Business
    Posts
    3

    Finding the max sum value out of multiple "groups"

    Hi all,

    Having an issue returning the maximum value of "groups" summed by certain criteria. Tried multiple variations of index, match and array formulas but have had no success yet. Stripped down the data is:
    A 100
    A 200
    B 100
    A 100
    B 50

    I want in 1 cell for it to work out the sum values of A & B are respectively 400 and 150 each and then return 400 as this is the highest value. Limitations:
    - I don't want to filter/re-arrange or change the table in any way as the actual data set is much more complicated.
    - I also don't want to directly refer to "A" or "B" with IFs or SUMIFs because the actual data set has 100+ variations
    - Would prefer not to use coding as I'm terrible with it.

    Any help would be greatly appreciated!

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Finding the max sum value out of multiple "groups"

    Hi -

    I have a solution that includes some helper columns you can put off to one side of your spreadsheet (or even on a separate spreadsheet in the same workbook). The first step is to create a list of unique names (or groups), so A, B and I added a C. I did this with an array formula in column D. It looks like this:

    =IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$11),0)),"")

    Then I used SUMPRODUCT (you can use SUMIF if you prefer - it is a little more efficient if you have a large spreadsheet) to add up the values by each Group. The formula looks like:

    =SUMPRODUCT(($A$2:$A$10=D2)*($B$2:$B$10))

    Then you can simply use the MAX command to find the largest value. I also added an INDEX/MATCH to find the name of the group with the largest value. Attached is a spreadsheet with all of the above.

    Hope this helps.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Finding the max sum value out of multiple "groups"

    a
    b
    c
    d
    e
    1
    a
    100
    a
    2
    a
    200
    b
    3
    b
    100
    400
    d3: {=max(sumif(a1:a5, d1:d2, b1:b5))}
    4
    a
    100
    5
    b
    50
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Finding the max sum value out of multiple "groups"

    Slower but simpler:

    A
    B
    C
    D
    E
    1
    A
    100
    400
    D1: {=MAX(SUMIF(A1:A5, A1:A5, B1:B5))}
    2
    A
    200
    3
    B
    100
    4
    A
    100
    5
    B
    50

  5. #5
    Registered User
    Join Date
    10-29-2019
    Location
    London, England
    MS-Off Ver
    365 Business
    Posts
    3

    Re: Finding the max sum value out of multiple "groups"

    Thank you for your help Login & Shg.

    With this second solution Shg is there a single-cell formula to return the name of the largest group as well?

  6. #6
    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,900

    Re: Finding the max sum value out of multiple "groups"

    1. ordinary formula to give MAX:
    =AGGREGATE(14,6,SUMIF(A1:A8, A1:A8, B1:B8),1)

    2. Ordinary formula to give group Name:
    =INDEX(A1:A5,MATCH(E1,INDEX(SUMIF(A1:A5,A1:A5,B1:B5),0),0))
    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

  7. #7
    Registered User
    Join Date
    10-29-2019
    Location
    London, England
    MS-Off Ver
    365 Business
    Posts
    3

    Re: Finding the max sum value out of multiple "groups"

    Thanks Glen!

    Apologies all but I have one more question- if I wanted to return the nth value what formula do I need to use? I tried large but doesn't seem to work.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Finding the max sum value out of multiple "groups"

    My feeling is that the easiest way to do this would be to use a pivot table (modeled in G2:H5), which could be placed out of sight for aesthetic purposes, to sum all of the groups.
    You could then use a set up similar to the following:
    Place the ordinal (nth) in cell E2
    To display the nth value place the following in cell E3: =AGGREGATE(14,6,H3:H8,E2)
    To display the group place the following in cell E4: =INDEX(G3:G8,MATCH(E3,H3:H8,0))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 7
    Last Post: 10-21-2019, 03:34 PM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. Replies: 8
    Last Post: 07-12-2017, 05:32 PM
  4. Code for finding most recent email with " " subject when multiple exist
    By RubiksCuber in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2016, 02:06 PM
  5. Replies: 12
    Last Post: 06-12-2014, 02:11 PM
  6. [SOLVED] Finding "last price" paid for multiple receipts in a large set of data
    By Rim2Rim in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-24-2014, 12:01 AM
  7. Replies: 2
    Last Post: 06-06-2013, 12:45 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