Quick, clean, and to the point

Sum first n matching values

Excel formula: Sum first n matching values
Generic formula 

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


where name (B5:B16) and score (C5:C16) are named ranges.


The FILTER function, new in Excel 365, is can help simplify some some tricky formula problems.

In this example, the goal is to sum the first 3 scores for both Jake and Hailey, based on the order they appear in the table. There are 12 scores total, and Jake and Hailey have 6 scores each.

Working from the inside out, the first task is generate a list of scores for each name. This is done with the FILTER function:


With "Jake" in cell F5, the result is an array with all of Jake's scores like this:


This array is returned to the INDEX function as the array argument:


 The SEQUENCE function is used to generate the value for row number, and returns an array with 3 numbers,

SEQUENCE(3,1,1,1) // returns {1;2;3}

At this point, we can write the INDEX portion of the formula like this:


INDEX returns values associated with the first 3 rows in the array to the SUM function:

=SUM({6;5;7}) // returns 18

and SUM returns sum of these values as the final result in G5. When the formula is copied down to cell G6, the result is the sum Hailey's first 3 scores.

Sum last n matching values

To sum the last n matching values, you can adapt the formula like this:


This formula is explained in more detail here.

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