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. The IF function takes three arguments like this: In this example, the logical test is the expression F5-E5>2: When this expression returns TRUE, the IF function calls the SUM function to sum values in data1: When the expression returns FALSE, IF calls the SUM function to sum values in data2: 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: To check if F5 and F5 are both greater than 3: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
