The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the...

The INDEX function looks up values by position. For example, this formula retrieves the value for Acme sales in Jan:
=INDEX(data,1,1)
The INDEX function has a special and non-obvious behavior: when the row number...

At the core, this formula has a simple pattern like this:
=SUM(first:current)
Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column.
To...

The core of this formula is COUNTIF, which is configured to count three separate values using wildcards:
COUNTIF(B5,{"x*","y*","z*"}
The asterisk (*) is a wildcard for one or more...

This formula uses structured references to refer to the "Amount" column in each table. The structured references in this formula resolve to normal references like this:
=SUM(Table1[Amount],Table2[Amount])
=SUM(C7:C11...

In its simplest form, SMALL will return the "nth smallest" value in a range. For example:
=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
However, if you supply an array...

In its simplest form, SMALL returns the "Nth smallest" value in a range with this construction:
=SMALL (range,N)
So, for example:
=SMALL (C5:C12,2)
will return the 2nd smallest value in the range C5:C12, which is...

The core of this formula uses the INDEX and MATCH function in a special way to return a full column instead of a single value. Working from the inside out, the MATCH function is used to find the correct column number...

Note: this example assumes that fuel is added to capacity at each gas stop, in order to calculate miles per gallon (MPG) based on the miles driven and fuel used since the last stop. In addition, this example keeps all...

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...

The challenge with lookup formulas that retrieve more than one match is managing duplicates (i.e. multiple matches). Lookup formulas like VLOOKUP and INDEX + MATCH can easily find the first match, but it's much harder...

In the example shown, we have monthly data for the years 2017 and 2018. The goal is to dynamically sum values through a given number of months, hardcoded as 6 in cell G5. This is done by feeding a reference constructed...

In this example, there are 5 rows of data for each week (Mon-Fri) so we want to sum every 5 rows. To build a range that corresponds to the right 5 rows in each week, we use the OFFSET function. In F4 we have:
OFFSET...

This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in...

At the core, this formula has uses INDEX to create an expanding reference like this:
INDEX([Color],1):[@Color] // expanding range
On the left side of the colon (:), the INDEX function returns a reference to the...