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

Excel times are fractional numbers. This means you can add times together with the SUM function to get total durations. However, you must take care to enter times with the right syntax and use a suitable time format to...

At the core, this formula uses the UNIQUE function to extract unique values, and the FILTER function apply criteria.
Working from the inside out, the FILTER function is used to apply criteria and extract only names...

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

In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula:
=LARGE(B4:B13, 2)
will return the 2nd largest value in the range B4:B13 which, in the example above, is the...

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

Excel supports "full column" and "full row" references like this:
=SUM(A:A) // sum all of column A
=SUM(3:3) // sum all of row 3
You can see how this works yourself by typing "A:A", "3:3", etc. into the name box (...

This formula uses what is called a "mixed reference" to create an "expanding range". A mixed reference is a reference that includes both absolute and relative parts.
In this case, the SUM formula refers to the range...

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

The core of this formula is COUNTIF, which returns zero if none of the substrings is found, and a positive number if at least one substring is found. The twist in this case is that we are giving COUNTIF more than one...

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

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

The key to understanding this formula is to realize that the INDEX function can be used to return a reference to entire rows and entire columns.
To generate a reference to the "last n columns" in a table, we build a...

The dropdown is set up with a simple data validation rule based on a "list":
Red,Blue,Green,All
The named ranges "color" (C5:C15) and "qty" (D5:D15) are for convenience only.
The formula in G5 performs a...

Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows a one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and...