Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use boolean logic to...

At the core, this formula uses the COUNTIF function to count any cells that fall below a given value, which is hardcoded as 65 in the formula:
COUNTIF(B5:F5,"<65")
In this part of the formula, COUNTIF...

In this example, the goal is to count the number of cells in a range that do not contain errors.
Working from the inside out, we first use the ISERROR function on the entire range:
ISERROR(B5:B14) // check all 10...

The behavior of the IF function can be easily extended by adding logical functions like AND, and OR, to the logical test. If you want to reverse existing logic, you can use the NOT function.
In the example shown, we...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 20...

In Excel, empty double quotes ("") mean empty string. The symbol is a logical operator that means "not equal to", so the following expression means "A1 is not empty":
=A1<>"" // A1 is not empty
This...

In this example, column D records the date a task was completed. Therefore, if the column contains a date (i.e. is not blank), we can assume the task is complete.
The formula in cell E5 uses the IF function to check...

When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply...

In this example, we need to construct logic that filters data to include:
account begins with "x" AND region is "east", and month is NOT April.
The filtering logic of this formula (the include argument) is created by...

When conditional formatting is applied with a formula, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the active cell when the rule is created is...

This formula uses the IF function to check for "complete" in column C. When a cell contains "complete", IF returns
CHAR(252)
which displays a checkmark when the font is "Wingdings". When a cell contains any other...

This formula uses boolean logic to "filter" the numbers in column C before summing with the SUMPRODUCT function. When the formula is evaluated, values in the named range "sales" are expanded into an array:
{925;1038;...