Course summary: Advanced IF functions

Use the TRIM function to avoid unintended mistakes

If the text in a formula has extra spaces, comparing text with a function may not return the results you expect and could result in some pretty big miscalculations.

Adding the TRIM function to a formula removes spaces from a text string except for single spaces between words.

Nested IF functions

You can always tell when a function is nested because it's inside parentheses. Nested IF functions, meaning one IF function inside of another, allow you to test multiple criteria and increases the number of possible outcomes.

COUNTIFS and SUMIFS

COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. This is the syntax of the COUNTIFS function.

SUMIFS adds the cells in a range that meet multiple criteria.

AVERAGEIFS and IFERROR

AVERAGEIFS returns the average of all cells that meet multiple criteria. IFERROR returns the value specified if AVERAGEIFS evaluates to an error.

See also

 
 
Applies to:
Excel 2013