+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    06-21-2010
    Location
    Curacao, NA
    MS-Off Ver
    Excel 2007
    Posts
    2

    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!
    Last edited by Yangado; 08-30-2015 at 09:00 AM. Reason: Solved

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,130

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

    hi Yangado. try adding the MATCH.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    06-21-2010
    Location
    Curacao, NA
    MS-Off Ver
    Excel 2007
    Posts
    2

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

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Last edited by Tony Valko; 08-30-2015 at 03:02 PM.

+ 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. Copied data and array formula returns #DIV/0!
    By gnikoli in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-31-2014, 11:53 PM
  2. [SOLVED] If formula that returns a value from an associated column array
    By ensmith in forum Excel General
    Replies: 3
    Last Post: 06-29-2012, 12:26 PM
  3. Array formula returns a 0, but I don't want it to
    By keys in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-08-2008, 07:29 PM
  4. Combo Box returns from array formula
    By robcosta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2008, 03:33 PM
  5. Replies: 2
    Last Post: 07-10-2008, 05:53 AM
  6. Array formula returns 0
    By erict in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2007, 02:23 PM
  7. Replies: 2
    Last Post: 08-02-2006, 06:10 PM

Tags for this Thread

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