What do criteria do?
Otherwise, return the original price.
What are criteria?
Criteria are logical expressions that return TRUE or FALSE, or their numerical equivalents, 1 or 0.
In all other cases, criteria should return FALSE or zero.
Logical operators
Criteria often make use of the logical operators listed in the table below.
Logical operators can be combined in various ways, as seen in the examples below.
The table below lists the key logical functions.
Multiple criteria
Naturally, there are many cases where you will want to use multiple criteria.
Not all functions allow wildcards.
Here is a list of common functions that do:
Notice the IF function isnoton this list.
To get wildcard behavior with IF, you could combine the SEARCH and ISNUMBER functions, as described below.
Testing criteria
The classic way to test criteria is to wrap them in the IF function.
Otherwise, return nothing.
However, you’ve got the option to also test criteria directly on the worksheet as a formula.
Let’s say you want to process values that are 80 and higher.
In the screen below, C3 contains this formula, copied down.
Translation: the value in B3 is greater than or equal to 80.
Don’t be thrown off by the equals (=) sign when testing criteria as a formula.
All Excel formulas must begin with an equals sign, so it must be included.
Remove the equal sign when you move criteria into another formula.
Another way to test criteria is to use F9 to evaluate the criteria in place.
Just carefully select a logical expression, and press F9.
Excel will immediately evaluate the expression and display the result.
Video:How to use F9 to debug a formula.
To do that, just remove the equal sign and add the criteria where needed in the formula.
Otherwise, return “Fail”.
This formula is useful when testing cells that may contain formulas that return empty strings ("").
“red”).
Also, remember thatnumber formattingin Excel affects display only, and does not change numeric data in any way.
For more granular work, Excel has special functions to extract time by component.
Here are some examples:
Criteria for SUMIFS, COUNTIFS, etc.
The criteria for SUMIFS, COUNTIFS, AVERAGEIFS, and similar range-based functions follow slightly different rules.
Simple criteria based on equality don’t need special handling.
These are required when criteria include an operator in these functions.
Criteria for data types
Excel allows three main data types: text, numbers, and logicals.
By default, numbers are right-aligned, text is left-aligned, and logical values are centered.
However a user can override alignment manually, so this is not a good test of bang out.
Excel provides three functions you might use to check data types: ISTEXT, ISNUMBER, and ISLOGICAL.
These functions return TRUE or FALSE.
This section explores a few techniques.
Note that the reference to $E$3 isabsoluteto prevent changes as the formula is copied down.
Making criteria variable in COUNTIFS, SUMIFS, etc.
For functions that support wildcards (like COUNTIFS, SUMIFS, etc.
), you’re able to use wildcards to do this.
This works because SEARCH returns a numeric position if “red” is found, and ISNUMBER returns TRUE.
If not, SEARCH returns an error, and ISNUMBER returns FALSE.
For more details, seethis page.
Nested IFs
Nested IF formulas are often used to check multiple criteria and return multiple results.
In general, the challenge is to build nested IFs so that the criteria appear in the right sequence.
They can sometimes be used as criteria to create simple OR logic criteria.
Because we give SUMIFS two values for criteria, it returns two results.
The SUM function then returns the sum of the two results.
Note: this is an array formula and must be entered with control + shift + enter.
The result is the top value for each region.
Values associated with the “East” region are FALSE.
The MAX function then returns the largest value in the array, ignoring all FALSE values.
Advanced formula criteria
Below are links to more advanced formula criteria examples.
Each link has a screenshot and a full explanation.
More formula resources
The following links contain more detailed information on Excel formulas:
