# Finding the max sum value out of multiple "groups"

1. ## 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. ## 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.

3. ## 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

4. ## 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. ## Re: Finding the max sum value out of multiple "groups"

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

6. ## 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))

7. ## 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. ## 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.

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

#### 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