Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:...Read more
In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong...Read more
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:
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