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

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

The syntax for referencing a range of sheets is a built-in feature and works a bit like a reference to a range of cells. For example
Sheet1:Sheet3!A1
Means: cell A1 from Sheet1 to Sheet3.
In the example shown:
=...

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

A weighted average, also called a weighted mean, is an average where some values count more than others. In other words, some values have more "weight". We can calculate a weighted average by multiplying the values 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...

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 does not provide a dedicated "contains" function, but you can create a custom function to test if a cell contains one or many strings with the LAMBDA function. LAMBDA functions do not require VBA, but are only...

There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function, but SUBTOTAL only works with vertical ranges. As a result, the approach described in this...

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

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

There are two requirements that must be satisfied, and both evaluated inside a single AND statement. The first requirement is at least 5 volunteer hours in each of the 3 categories: A, B, and C. This requirement is...