IF with other calculations

The IF function can be combined with other calculations by nesting other formulas and functions inside IF. In the example shown, the formula in G5, copied down, is:

=IF(F5-E5>2,SUM(data1),SUM(data2))

where data1 (B5:B14) and data2 (C5:C14) are named ranges.

When the result of F5-E5 is greater than 2, the IF function returns the sum of values in data1. When the result of F5-E5 is not greater than 2, IF returns the SUM of values in data2.

How this formula works

The purpose of this formula is to demonstrate how other formulas and functions can be nested inside the IF function.

The IF function takes three arguments like this:

=IF(logical_test, value_if_true, value_if_false)

In this example, the logical test is the expression F5-E5>2:

=IF(F5-E5>2 // logical test

When this expression returns TRUE, the IF function calls the SUM function to sum values in data1:

SUM(data1) // when test is TRUE

When the expression returns FALSE, IF calls the SUM function to sum values in data2:

SUM(data2) // when test is false

In either case, the SUM function returns a value to the IF function, and IF returns that value as the final result.

Other calculations

This concept can be customized any way you like. To return an average instead of a sum:

=IF(F5-E5>2,AVERAGE(data1),AVERAGE(data2))

To check if F5 and F5 are both greater than 3:

=IF(AND(F5>3,E5>3),SUM(data1),SUM(data2))

And so on. The point is you can do any calculations you like inside IF. You can even create a nested IF.

Note: nesting other calculations inside a function or formula is a common practice in many more advanced formulas. It is not limited to the IF function. You can find many examples in this list.