Quick, clean, and to the point

Sum top n values with criteria

Excel formula: Sum top n values with criteria
Generic formula 

To sum the largest n values in a range with criteria, you can use a formula based on FILTER, LARGE, and SUM. In the example shown, the formula in cell F5 is:


where data is an Excel Table in the range B5:C16. The result is the sum of the top 3 values in Group A (65,45,25) which is 135.

Note: FILTER is a newer function not available in all versions of Excel. See below for an alternative formula that works in older versions.


In this example, the goal is to sum the largest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three largest values, so n is equal to 3. At a high level, this problem breaks down into three separate steps:

  1. Apply criteria to select specific values
  2. Extract the 3 largest values
  3. Sum the 3 extracted values

This problem can be solved with a formula based on the FILTER function, the LARGE function, and the SUM function. For convenience, the range B5:C16 is an Excel Table named "data". This allows the formula to use structured references.

Note: FILTER is a newer function not available in "Legacy Excel". See below for an alternative formula that works in older versions of Excel.

Example formula

The final formula in cell F5 is:


To explain how this formula works, we'll walk through the steps listed above. This means we will be working through the formula from the inside out. This is typical of Excel formulas where one function is nested inside another.

Apply criteria

The first step in the problem is to apply criteria to select values by group. This can be done with the FILTER function. To select values in group "A", we can use FILTER like this:


In this formula array is provided as the Values column in the table, and the include argument provides logic to select only values in group "A". The result is a subset of the values where the group is "A", which is returned as an array like this:


If you are new to the FILTER function, see this video: Basic FILTER function example

Extract 3 largest values

The next step in the problem is to extract the three largest values from the array returned by FILTER. For this, we use the LARGE function. The LARGE function is designed to return the nth largest value in a range. For example:

=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest

Normally, LARGE returns just one value. However, if you supply an array constant (e.g. a constant in the form {1,2,3}) to LARGE as the second argument, k, LARGE will return an array of results instead of a single result. For example:

=LARGE(range,{1,2,3}) // largest 3 values

Picking up where we left off, FILTER is used to collect values in group "A". The results returned by FILTER are returned directly to the LARGE function like this:

LARGE({10;65;25;45;20;15},{1,2,3}) // returns {65,45,25}

The LARGE function then returns the 3 largest values in an array: 65, 45, 25.

Sum largest values

The final step in problem is to sum the values extracted by the LARGE function. This is done with the SUM function:

=SUM({65,45,25}) // returns 135

SUM returns 135 as a final result, which is the sum of the largest 3 values in group A.

When n becomes large

As n becomes a larger number, it becomes tedious to enter longer array constants like {1,2,3,4,5,6,7,8,9,10}, etc. In this case, you can use the SEQUENCE function to generate an array of sequential numbers automatically like this:


Just replace n with the number of largest values you want to extract:

=SUM(LARGE(FILTER(values,criteria),SEQUENCE(3)) // largest 3
=SUM(LARGE(FILTER(values,criteria),SEQUENCE(5)) // largest 5

Legacy Excel

In older versions of Excel that don't provide the FILTER function, you can use the IF function to "filter" data like this:


The behavior of this formula is much the same as the original formula above. The main difference is that the IF function returns values from group A when the group="A", but it returns FALSE when the group does not match the supplied criteria. So the LARGE function receives an array from IF that looks like this:


Unlike the FILTER function, which returned just the six values associated with group A, IF returns an array that includes twelve results, one for each value in the original data. However, because LARGE is programmed to automatically ignore the logical values TRUE and FALSE, the result from LARGE, {65,45,25},  is the same as before and the final result is correct:

=SUM({65,45,25}) // returns 135

Note: this an array formula and must be entered with Control + Shift + Enter in Legacy Excel.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.