Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a range to ISNUMBER (i.e. an array), ISNUMBER will return an array of results. In the example,...

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

In this example, the goal is to count cells that contain a specific substring. This problem can be solved with the SUMPRODUCT function or the COUNTIF function. Both approaches are explained below. The SUMPRODUCT version...

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is an expression based...

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 number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH...

Excel's formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:
=90>100 // returns FALSE
="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 goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal "contains" problem...

In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found. In other words, we want check for the colors seen in column E and list the colors...

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

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

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

Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the FIND function is configured to search for the text "XST" in cell C5. If found, FIND will return a numeric position (i....

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