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

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

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

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

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

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

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

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

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

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

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

By default, the SUMIFS function only allows AND logic – when you provide multiple conditions, all conditions must match to be included in the result.
One solution is to supply multiple criteria in an array constant...

Data validation rules are triggered when a user adds or changes a cell value.
In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM...