Results 1 to 5 of 5

Getting a the max value of an array of the sums of multiple columns? Is it possible?

Threaded View

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003, 2007, & 2010
    Posts
    52

    Question Getting a the max value of an array of the sums of multiple columns? Is it possible?

    Having a hard time thinking of a method of doing this within a single cell. Hopefully you guys/gals can help. Basically what I am looking to do is find a way to create a dynamic array of the sums of individual columns and return the maximum value in that array. Below is the best I could come up with but it is only dynamic in height. I would like the quantity of columns to also be dynamic. The example code below is using a range of A1:E5. An example would be if each column is a Day and each row is a quantity I want to know what the maximum total quantity for a single day is.

    =MAX(
    SUM(OFFSET(A1,0,0,Num_Of_Rows)),
    SUM(OFFSET(A1,0,1,Num_Of_Rows)),
    SUM(OFFSET(A1,0,2,Num_Of_Rows)),
    SUM(OFFSET(A1,0,3,Num_Of_Rows)),
    SUM(OFFSET(A1,0,4,Num_Of_Rows)))
    The easy way would be to have an additional row of totals and which have a dynamic height [i.e. =sum(offset(A1,0,0,Num_Of_Rows))] and then use a separate cell to set a dynamic width across the total row for the maximum [i.e. =max(offset(A6,0,0,0,Num_Of_Columns))] but I don't have the ability to have a totals row here.

    Hopefully there is another solution you can help me find. Thanks.

    FYI this has been crossposted here:
    Getting a the max value of an array of the sums of multiple columns? Is it possible?
    Last edited by drew.j.harrison; 06-16-2015 at 06:36 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Subtotals/sums based on criteria in multiple columns
    By pcaldwell in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2015, 03:12 PM
  2. [SOLVED] Sum Formula based on single or multiple critera that sums accross and array
    By Dial1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2013, 07:14 PM
  3. Multiple Sums into one ROW from various Columns
    By vitaminallan in forum Excel General
    Replies: 12
    Last Post: 03-26-2012, 06:04 AM
  4. Array on multiple columns
    By Yot in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-04-2011, 05:35 PM
  5. SUM array with multiple columns
    By carsto in forum Excel General
    Replies: 1
    Last Post: 10-31-2006, 03:13 AM

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