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.
Bookmarks