# Formula Challenge 022 - Find the item with the largest sum in a list

#### Lori

##### Active Member
Given a single column list containing text items with numbers following, find the item with the largest sum.

For example in the following list your formula should return c (with a sum of 9).

a
1
3
b
1
2
5
c
2
7
d
8

Your answer should be a single formula involving Excel functions only that refers to a single named range x containing the list of values.

You may assume numbers are positive. Good luck!

#### Sajan

##### Excel Ninja
Hi,
Good challenge!

I am sure the following array formulas can be optimized, but here is what I have so far:

=INDEX(x,MATCH(MAX(MMULT(TRANSPOSE(ROW(x)^0),IFERROR((LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x)=TRANSPOSE(x))*x,0))),MMULT(TRANSPOSE(ROW(x)^0),IFERROR((LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x)=TRANSPOSE(x))*x,0)),0))
enter with Ctrl + Shift + Enter

if only integer numbers can be assumed, then the following array formula would also work:
=INDEX(x,ROUND(MOD(MAX(MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)+MATCH(ROW(x),ROW(x),0)%),1)*100,0))
enter with Ctrl + Shift + Enter

Cheers,
Sajan.

#### Lori

##### Active Member
Sajan - That's a great solution and you got there much quicker than me. I see ideas from the previous challenge being implemented here.

As you say, there are possibilities for further efficiency improvements. One way is to use the FREQUENCY function:
a) to pick out the largest sum (which should save repeating the formula for the array of sums)
b) to find the length of the runs of numbers to sum (which should be quicker than the matrix approach.)

Mine runs to 120 characters, but there may well be shorter versions.

#### Sajan

##### Excel Ninja
Hi Lori,
I look forward to seeing your FREQUENCY based solution.

Having seen a few (unique) examples of your formulas elsewhere, I am a fan!

Regards,
-Sajan.

#### Sam Mathai Chacko

##### Active Member
Well, I'll hope I can pay up for this hectare space some day, but I'll go with this mega formula.

=INDEX(X,INDEX(SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),MATCH(MAX(SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0")),SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)))

• Thomas Kuriakose

#### Abhijeet R. Joshi

##### Active Member

@Sajan: I have seen you often use the MMULT Formulas and the (^) sign, what are these used for? Something specific?

#### Sam Mathai Chacko

##### Active Member
Abhijeet, basically, this is what my formula is doing

=INDEX(X,INDEX({1,4,8,11},MATCH(MAX(SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>")),SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>"),)))

I'm sure you can make out what's happening in there now. • Abhijeet R. Joshi

#### Sajan

##### Excel Ninja
Hello Abhi,
MMULT stands for Matrix Multiplication. (As you may recall, a matrix is an array with one or more dimensions.)

The ^ is the exponentiation operator. In the above formula, a value is being raised to the power of zero, which you may recall is 1. So it is just a technique to build an array of 1s like {1;1;1;1;1;1;1}

Cheers,
Sajan.

• Abhijeet R. Joshi

#### Abhijeet R. Joshi

##### Active Member
Thanks Sajan....thats very useful....would love to learn excel from you guys....You guys are way too awsome....

#### Xiq

##### Active Member
I would love to see a few more blog posts on how a these amazing challenge-formula's work! (Like the Formula Challenge 001 series)

#### Luke M

##### Excel Ninja
Hello Abhi,
MMULT stands for Matrix Multiplication. (As you may recall, a matrix is an array with one or more dimensions.)

The ^ is the exponentiation operator. In the above formula, a value is being raised to the power of zero, which you may recall is 1. So it is just a technique to build an array of 1s like {1;1;1;1;1;1;1}

Cheers,
Sajan.
Any chance you would want to write up a detailed article either for the blog, or here on the forum about this nifty little function? I tool have seen you use it often, but do not fully understand how it works or how to make it do these tricks (similar to how one may know what SUMPRODUCT literally does, but not understand that it can check criterias)

• Abhijeet R. Joshi

#### Lori

##### Active Member
Well, I'll hope I can pay up for this hectare space some day, but I'll go with this mega formula.

=INDEX(X,INDEX(SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),MATCH(MAX(SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0")),SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)))
Sam - That seems to work well for the first row, but maybe a small adjustment is needed in case the list is located elsewhere. Here's some further clues relating to my suggestions above (to help save on costly real estate ):
a) FREQUENCY(-L,-y)
b) FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x))
where L is a large number and y is a numeric array.

• NARAYANK991

#### Sajan

##### Excel Ninja
Hi Luke,
I had drafted an article on MMULT a while back, but never published it. Perhaps I should "dust it up" and submit for consideration to Hui, Chandoo, etc. Let me know if you would like to receive a draft copy for early feedback or comments. (As an admin on the site, you can probably get my e-mail from my profile.)

Regards,
Sajan

• Luke M

#### Sajan

##### Excel Ninja
Hi Lori,
That is a cool trick with FREQUENCY

Here is a revised formula (which I will now look to optimize further):
=INDEX(x,MATCH(1,FREQUENCY(-9999, -MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)),0))

enter with Ctrl + Shift + Enter

Thanks!
-Sajan.

• Thomas Kuriakose and NARAYANK991

#### Sajan

##### Excel Ninja
Hi,
Replacing INDEX(MATCH()) with LOOKUP shortens it a bit more, but I am sure this can be optimized further:
=LOOKUP(1,1/FREQUENCY(-9999,-MMULT(IFERROR((TRANSPOSE(LOOKUP(ROW(x),1/ISERR(-x)*ROW(x),x))=x)*TRANSPOSE(x),0),ROW(x)^0)),x)

enter with Ctrl + Shift + Enter

-Sajan.

• NARAYANK991

#### Sam Mathai Chacko

##### Active Member
Lori, yes I agree about the row location. But thanks a lot for that innovative use of FREQUENCY. That would have never occured to me!

Sajan, here's a shorter version of your formula

=LOOKUP(1,1/FREQUENCY(-9^9,-MMULT((TRANSPOSE(LOOKUP(ROW(X),ROW(X)/ISERR(-X),X))=X)*N(TRANSPOSE(X)),ROW(X)^0)),X)

• Thomas Kuriakose and NARAYANK991

#### Sajan

##### Excel Ninja
Hi Sam,
Here is another revision that shortens the formula a bit more:
=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x),x))=x),IFERROR(-x,0))),x)

entered with Ctrl + Shift + Enter

-Sajan.

• shrivallabha and NARAYANK991

#### Sam Mathai Chacko

##### Active Member IFERROR(someerror,) returns 0

=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(X),ROW(X)/ISERR(-X),X))=X),IFERROR(-X,))),X)

• Thomas Kuriakose and NARAYANK991

#### NARAYANK991

##### Excel Ninja
Hi Lori ,

Can you say what should be the output in the following data ?

Code:
``````  p
311
a
p
33
44
155
66
17
r
d
e
399
s``````
Should it be p or e ?

Narayan

#### Sam Mathai Chacko

##### Active Member
Interesting find Narayan. Made me add an IFERROR() to my original mega formula (which by the way still falls under Lori's radar of row location, but is still effective) =INDEX(X,INDEX(SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),MATCH(MAX(IFERROR(SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)),SUMIF(OFFSET(X,SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),,IFERROR(SMALL(IF(ISTEXT(OFFSET(X,1,)),ROW(OFFSET(X,1,))),ROW(INDIRECT("1:"&SUM(--ISTEXT(X)))))-1,ROWS(X))-SMALL(IF(ISTEXT(X),ROW(X)),ROW(INDIRECT("1:"&SUM(--ISTEXT(X))))),),"<>0"),)))

#### Sajan

##### Excel Ninja
Hi Narayan,
By tweaking the above formula slightly, we can get the string with the largest sum, instead of the cumulative sum.

=LOOKUP(1,1/FREQUENCY(-9^9,MMULT(N(TRANSPOSE(LOOKUP(ROW(x),ROW(x)/ISERR(-x)))=ROW(x)),IFERROR(-x,0))),x)

enter with Ctrl + Shift + Enter

Regards,
Sajan.

• Lori and NARAYANK991

#### Lori

##### Active Member
Sajan (& Sam) - Nice tweaks you definitely win on formula length now! I'll post my approach in a day or two...

Narayank991 - That's a very valid point. I'd be happy to accept either approach (p or e in your example) providing it's consistent and meets the initial spec which those posted so far do.

I should have made explicit that the text values didn't repeat and also that blanks should be ignored and not treated as headers. (The question was actually based on a real world example, where data came from a long report in list format where the headers were profit centres that were known to be unique.)

#### Lori

##### Active Member
Abhijeet, basically, this is what my formula is doing

=INDEX(X,INDEX({1,4,8,11},MATCH(MAX(SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>")),SUMIF(OFFSET(X,{1,4,8,11},,{2,3,2,1},),"<>"),)))

I'm sure you can make out what's happening in there now. Sam, can you refine this idea? The result may be a few characters longer than the other way but is comparatively very efficient and could easily be extended to many thousands of rows.

Hint: Try replacing the {2,3,2,1} array with my other suggestion above and {1,4,8,11} with ROW(x).

• Sam Mathai Chacko

#### Sajan

##### Excel Ninja
Ripe for optimization, but here is the approach that Lori is outlining:

=LOOKUP(1,1/FREQUENCY(-9^9,-IFERROR(SUMIF(OFFSET(x,ROW(x)-ROW(INDEX(x,1)),,FREQUENCY(-ROW(x),-ISTEXT(x)*ROW(x)),1),"<>"),0)),x)

• Sam Mathai Chacko

#### Sam Mathai Chacko

##### Active Member
Thanks Lori.

I like that Sajan. I was sure the SUMIF could have been used better, but this is cool.