The MATCH function checks all values in the named range "data" against values in "filter"
ISNUMBER converts matched values to TRUE and non-matched values to FALSE
IF uses the array output in #2 above to filter...

This formula depends on a TRUE or FALSE result from a logical test, where FALSE represents the value you are looking for. In the example, the logical test is data="red", entered as the lookup_array argument in the...

In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the...

Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.
The core of this formula is the ISNUMBER + SEARCH...

The SEARCH function returns the position of the search string when found, and the #VALUE! error if not found. We use this fact to test whether the search string is found by using the ISNUMBER function to "catch" valid...

In this example, the goal is to sum the numbers in column C when the item in column B is listed in the range E5:E7. One way to accomplish this is to give the SUMIF function all three values in the named range things (E5...

When you count cells with "OR logic", you need to be careful not to double count. For example, if you are counting cells that contain "abc" or "def", you can't just add together two COUNTIF functions, because you may...

Data validation rules are triggered when a user adds or changes a cell value.
The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation.
Be aware...

The core of this formula is the INDEX function, with AGGREGATE used to figure out the "nth match" for each row in the extract area:
INDEX(data,nth_match_formula)
Almost all of the work is in figuring out and...

Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.
The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just...

The goal in this example is to use a formula to report which things exist a cell. The list of things to check for is in the named range things (E5:E9). The result is returned as a comma separated text string.
The first...

In this example, the goal is to count "z" or "c" values in the named range data, but only when the column header is "A" or "B". The formula used to perform this calculation is based on the SUMPRODUCT function:
=...

Data validation rules are triggered when a user adds or changes a cell value.
This formula uses the SEARCH function to test user input for each value in the named range "list". The search logic is "contains" — when a...

Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need...

The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so:
=IF(ISNUMBER(B4)
For any number less than the value in "input", the formula will return TRUE and the...