When you sum cells with "OR" criteria, you need to be careful not to double count when there is a possibility that both criteria will return true. In the example shown, we want to sum values in Column C when cells in...

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

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

This formula is based on the simpler formula described here. It uses the SEARCH function to look inside B5 for using cell C4 as the search string. If SEARCH returns a number, it means the search string was found. In...

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

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include".
In the right SUMPRODUCT, SEARCH...

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

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

This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:
1/(ISNUMBER(FIND(G6,files)))
Inside this snippet, the FIND...

This formula uses two named ranges: things, and results. If you are porting this formula directly, be sure to use named ranges with the same names (defined based on your data). If you don't want to use named ranges,...

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

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

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