Exceljet

Quick, clean, and to the point

Sum first n matching values

Excel formula: Sum first n matching values
Generic formula 
=SUM(INDEX(FILTER(range,logic),SEQUENCE(n,1,1,1)))
Summary 

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:

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

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

Explanation 

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.

Dynamic Array Formulas are available in Excel 365 only.
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 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.