# Array formula that returns the number of the row with the greatest sum

1. ## Array formula that returns the number of the row with the greatest sum

Hi all,
I have a data sheet with 10 rows and 7 columns starting in A1. The question is to write a formula which gives the row number where the sum of that particular row is the highest (max).
But while working with helping columns I can easily solve this issue, I've been tasked to find one array formula to return this value, so one formula only.
Any idea on how to do this? I thought that this might do the trick:

{=MAX(SUBTOTAL(9,OFFSET(Data,ROW(Data)-ROW(A1),0,1)))}

but it gives me the actual sum of the row with the maximum sum: not the row itself.
Any help is appreciated, thanks!  Register To Reply

2. ## Re: Array formula that returns the number of the row with the greatest sum

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Array formula that returns the number of the row with the greatest sum

WOWW! Thanks so much! That did the trick.
Ariadne   Register To Reply

4. ## Re: Array formula that returns the number of the row with the greatest sum

Do you want the row number relative to the range or do you want the absolute row number?

If this was your data what result do you expect:

Data Range
 A B C D 1 2 3 ------ ------ ------ ------ 4 5 1 1 1 1 6 2 1 1 2 7 100 100 100 100 8 2 2 2 2 9 3 3 9 3 10 1 4 3 5 11

3 (the relative row number) or 7 (the absolute row number)  Register To Reply

5. ## Re: Array formula that returns the number of the row with the greatest sum

Here's another one that returns the relative row number.

Data Range
 A B C D 1 Row 3 2 3 ------ ------ ------ ------ 4 5 1 1 1 1 6 2 1 1 2 7 100 100 100 100 8 2 2 2 2 9 3 3 9 3 10 1 4 3 5

This array formula** entered in B1:

=MATCH(MAX(MMULT(A5:D10,TRANSPOSE(COLUMN(A5:D10)^0))),MMULT(A5:D10,TRANSPOSE(COLUMN(A5:D10)^0)),0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply

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