Dave Bruns

Conditional formatting rules are evaluated in order.

1. For each cell in the range B5:B12, the first formula is evaluated. If the value is greater than or equal to 90%, the formula returns TRUE and the green fill is applied. If the value is not greater than or equal to 90%, the
...Read more

In this example, the goal is to create a count of each color in column B. The simplest way to solve this problem is with the COUNTIFS function.

COUNTIFS function

The COUNTIFS function returns the count of cells that meet one or...Read more

In this example, the goal is to return the greater of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MAX function.

MAX function

The MAX...Read more

In this example, the goal is to return the smaller of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MIN function.

MIN function

The MIN...Read more

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 looks for all strings in the named range "exclude".

In both...Read more

In this example, the goal is to sum the amounts in column C by week, using the dates in the range E5:E10 which are all Mondays. All data is in an Excel Table named data in the range B5:C16. This problem can be solved in a straightforward way with the...Read more

In this example, the goal is to construct a data validation rule that will prevent any one of a list of values from being entered. Data validation rules are triggered when a user adds or changes a cell value. One option is to use a formula to validate user input, which is the approach taken...Read more

Data validation rules are triggered when a user adds or changes a cell value.

In this case, the COUNTIF function is part of an expression that returns TRUE when a value does not exist in a defined list. The COUNTIF function simply counts occurrences of the value in the list. As...Read more

In this example, the goal is to retrieve the largest 3 (top 3) values in the named range data, which appears in the range B6:B16. The standard solution to get "nth largest values" is the LARGE function. However, one potential problem with LARGE is that it will return duplicate...Read more

The core of this formula is the IF function, which "filters" the names in the table by color like this:

IF(group=E5,name,""))

The logical test checks each cell in the named range "group" for the color value in E5 (red in this case). The...Read more