Sum first n matching values

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:

INDEX({6;5;7;7;6;8},SEQUENCE(3,1,1,1))

 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:

=SUM(INDEX(FILTER(score,name=F5),SEQUENCE(3,1,SUM(--(name=F5)),-1)))

This formula is explained in more detail here.