Sum first n matching values

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.