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

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

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

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

WOWW! Thanks so much! That did the trick.
Have a splendid week ahead!

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)

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.

##### Users Browsing this Thread

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