+ Reply to Thread
Results 1 to 2 of 2

Using Sum with Large

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    San Antonio, TX
    MS-Off Ver
    Excel for Office 365
    Posts
    4

    Using Sum with Large

    Is there a way to use SUM within the LARGE function? Helper columns and VBA aren't an option, I need to add a formula to a single cell.
    For example, given:

    Group Area Value LargeGroupValue
    1 a 8.0 9.1
    2 b 0.2
    2 c 6.1
    3 d 1.3
    3 e 7.7
    3 f 0.1

    Attempts:
    =LARGE(C2, Sum(C3:C4),SUM(C5:C7),1) -- error: "Too many arguments"
    =LARGE({C2;SUM(C3:C4);SUM(C5:C7)},1) -- error: "There's a problem with this function"

    The requirement is to find the larger of B2, the sum of B3:B4, or the sum of B5:B6. I've also tried the formulas with various permutations of INDIRECT. I've had no success with either standard or array formulas. I've seen related posts using "SUMIF" but they don't discuss multiple ranges. Would appreciate any advice. I've attached a sample worksheet. I realize this can be done with a series of nested IF functions, but as the dataset and groupings increase LARGE seems like it might work without cascading IF formulas. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Using Sum with Large

    Your description in the file is a bit confused - this is what you are asking for in cell G1:

    D2: =MAX(C4,SUM(C5:C6),SUM(C7:C8))

    which returns 9, but this is what you are trying to do in cells G2 and G3:

    D2: =MAX(C2,SUM(C3:C4),SUM(C5:C7))

    which returns 9.1

    Hope this helps.

    Pete
    Last edited by Pete_UK; 11-07-2019 at 01:29 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. Replies: 1
    Last Post: 12-19-2018, 04:47 PM
  2. Replies: 6
    Last Post: 11-22-2018, 05:18 AM
  3. [SOLVED] Request for Excel formula for two conditions (Large to small, then small to large)
    By nicholascky in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 08-14-2016, 01:16 PM
  4. adding a large, LARGE amount of text to a spreadsheet
    By Wolfpackfan in forum Excel General
    Replies: 4
    Last Post: 05-30-2016, 01:30 PM
  5. Replies: 5
    Last Post: 07-19-2015, 11:05 AM
  6. Help replacing large amount of text with another large amount of text??
    By floydrob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2012, 05:01 AM
  7. Large Form Help: Handling large number of text boxes and labels
    By laterdaysluke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2010, 03:12 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