Summing the Largest Values

Paul has a list of transactions in a worksheet. Column G contains the amount for each transaction. These transactions can be positive or negative. He needs to derive two sums from the transaction list: the seven largest positive transactions and the seven largest negative transactions. Paul just needs the sums, not any other information about the transactions.

The traditional way to find the largest and smallest values is to use the LARGE and SMALL functions, in this manner:

=SUM(LARGE(G:G,{1,2,3,4,5,6,7}))
=SUM(SMALL(G:G,{1,2,3,4,5,6,7}))

These formulas should work in all versions of Excel. The only thing to keep in mind is that they must be entered as array formulas (Ctrl+Shift+Enter) in Excel 2016 or older versions.

The problem with this approach is that it simply uses the largest and smallest values in the column. It doesn't take into account if those values are actually positive or negative. For example, it will work great if the largest values are {99, 54, 43, 43, 39, 23, 21}. It will not work well, though, if the largest values are {539, 94, 89, -1, -3, -11}. It will sum them, but Paul wanted the seven largest positive values, not just the seven largest values. In this case, there are not seven positive values, so LARGE returns negative values, too. The same issue arises when the values returned by SMALL don't include just negative values.

To get around the "mixed sign" results, you can modify the formulas in this manner:

=SUM(LARGE(IF(G:G>0,G:G),{1,2,3,4,5,6,7}))
=SUM(SMALL(IF(G:G<0,G:G),{1,2,3,4,5,6,7}))

In this case, the values in column G will only be summed if they are positive or negative, respectively. If seven values cannot be returned for the sum, then the formulas return a #NUM! error. This would seem appropriate for Paul's stated purposes.

If you want a formula that returns a sum for up to 7 values, excluding negative or positive values as appropriate, then you need to construct formulas that will only work in Excel 365:

=LET(rng, TRIMRANGE(G:G), vals, FILTER(rng, ISNUMBER(rng) * (rng > 0)), SUM(TAKE(SORT(vals, 1, -1), 7)))
=LET(rng, TRIMRANGE(G:G), vals, FILTER(rng, ISNUMBER(rng) * (rng < 0)), SUM(TAKE(SORT(vals, 1, -1), -7)))

Notice that the formulas use the FILTER function to exclude any non-numeric values in a column (such as a column header or blank cells) and any values less than 0 or greater than 0, respectively.

It should be noted that there are variations on these formulas that could be easily put together. Most variations rely on different ways to get the sequence of 1 through 7, {1,2,3,4,5,6,7}, used in the earlier formulas. For instance, you could replace the array with ROW(1:7) or with SEQUENCE(7). The options, as they say, are multitudinous.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11165) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.