Exceljet

Quick, clean, and to the point

Sum first n matching values

Excel formula: Sum first n matching values
Generic formula 
=SUM(TAKE(FILTER(data,logic),n))
Summary 

To sum the first n matching values in a set of data, you can use a formula based on the FILTER and TAKE functions. In the example shown, the formula in cell G5, copied down, is:

=SUM(TAKE(FILTER(data[Qty],data[Color]=F5),3))

where data is an Excel Table in the range B5:C16 and n is 3. The result is the sum of quantity for the first 3 Red values.

Explanation 

In this example, the goal is to sum the first n matching values in a set of data. Specifically, we want to sum the first 3 values for both Red and Blue, based on the order they appear in the table. There are 12 values total; 6 entries each for Red and Blue. All data is in Excel Table named data in the range B5:C16.

Example formula

In the example shown, the formula in cell G5, copied down, is:

=SUM(TAKE(FILTER(data[Qty],data[Color]=F5),3))

Notice the value for n is hardcoded as 3. This formula is a good example of nesting one function inside another.

Extracting matching data

Working from the inside out, the first task is to extract a list of quantities by color. This is done with the FILTER function like this:

FILTER(data[Qty],data[Color]=F5)

With "Red" in cell F5, the result is an array that contains quantities associated with "Red":

{6;5;6;9;6;8}

Notice there are 6 numbers in this array, one for each entry where the color is Red.

Extract first 3 values

The next task is to extract just the first 3 values from the array returned by FILTER. This is done with the TAKE function. FILTER returns the array directly to the TAKE function as the array argument, with the rows argument hardcoded as 3:

TAKE({6;5;6;9;6;8},3) // returns {6;5;6}

The TAKE function then returns the first 3 values in the array:

{6;5;6}

Sum results

The last step in the problem is to sum the results from FILTER and TAKE. This is done with the SUM function. TAKE returns the first 3 values to SUM:

=SUM({6;5;6}) // returns 17

And SUM returns 17 as a final result. This is the sum of the first 3 quantities for "Red". When the formula is copied down to cell G6, we get a sum of the first 3 "Blue" quantities.

Sum last n matching values

To sum the last n matching values, simply change n to a negative number like this:

=SUM(TAKE(FILTER(data[Qty],data[Color]=F5),-3))

The TAKE function is explained in more detail here.

Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
Author 
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.