Dave Bruns

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 argument is supplied as...Read more

This formula relies on the standard behavior of the COUNTIF function. The range is C5:C8, the criteria is provided as not equals OK:

=COUNTIF(C5:C8,"<>ok")

The COUNTIF then returns a count of any cells that do not contain "OK...Read more

To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. When dates are grouped, they can be queried based on the numeric equivalent:

1. Grouped by month - use numbers 1-12
2. Grouped by quarter -
...Read more

To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level.

In this case, we want a subtotal of the "sales" field, so we provide the name the field in the first argument, and supply a reference to the pivot table...Read more

To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level.

In this case, we want the grand total of the "sales" field, so we simply provide the name the field in the first argument, and supply a reference to the...Read more

Conditional formatting rules are evaluated in order.

1. For each cell in the range B5:B12, the first formula is evaluated. If the value is greater than or equal to 90%, the formula returns TRUE and the green fill is applied. If the value is not greater than or equal to 90%, the
...Read more

In this example, the goal is to create a count of each color in column B. The simplest way to solve this problem is with the COUNTIFS function.

COUNTIFS function

The COUNTIFS function returns the count of cells that meet one or...Read more