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

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

In this example, we have a list of of colors in a named range called "things" (E5:E9). We want to check the text in column B to see if it contains any of these colors. If so, we want to return the name of the first...

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

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

The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula...

At the core, this is an INDEX and MATCH function.
Inside the MATCH function, we use the SEARCH function to search cells in column B for every listed keyword in the named range keywords (E5:E14):
SEARCH(keywords,B5...

The FILTER function can filter data using a logical expression provided as the "include" argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this:
=...

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 relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a...

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

Although FILTER is more commonly used to filter rows, you can also filter columns, the trick is to supply an array with the same number of columns as the source data. In this example, we construct the array we need...

The core of this formula is the MODE function, which returns the most frequently occurring number in a range or array. The rest of the formula just constructs a filtered array for MODE to use in each row. The expanding...

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, criteria are entered in the range F5:H6. The logic of the formula is:
item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)
The filtering logic of this formula (the...