Sum first n matching values

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:
FILTER(score,name=F5)
With "Jake" in cell F5, the result is an array with all of Jake's scores like this:
{6;5;7;7;6;8}
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({6;5;7;7;6;8},{1;2;3})
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.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.