How to Use Nested IF and SUM Formula in Excel (2 Easy Methods)

Introduction to Nested Formulas in Excel

Nesting simply implies combining functions such that one function controls the outcome of another. Here’s an example of a calculation that uses the SUM function nested in the IF function:

=IF(SUM(range)>0, “Valid”, “Not Valid”)

Inside the IF function, the SUM function sums up the range of values.
SUM(range)>0 is the logical_test of the IF If the value_if_TRUE, the function returns “Valid”, the value_if_False the function returns “Not Valid”.

How to Use a Nested IF and SUM Formula in Excel: 2 Ways

We have an Excel worksheet that contains information about several sales representatives of the Armani Group. The names of the sales representatives, the Sales in Quarters 1, 2, and 3 by the sales representatives are given in Columns B, C, D, and E respectively.

Method 1 – SUM Function Nested in the IF Function

We will sum up the sales that have been sold by Ralph in quarters 1, 2, and 3. Then, we will check whether his sales are Excellent or Good.

Steps:

Select cell F5.

Insert the following:

=IF(SUM(C6:E6)>100000,"Excellent","Good")

Formula Breakdown:

The SUM function sums up the range C6 to E6.
If the total sales by Ralph are greater than $100,000 then the IF function will return Excellent otherwise it returns Good.

Press Enter on your keyboard.

AutoFill the SUM function that is nested in the IF function to the rest of the cells in column F.

Method 2 – IF Function Nested in the SUM Function

We will use the IF function, to sum up the conditional sales that have been sold by Ralph in quarters 1, 2, and 3. We will then sum up the total conditional sales in quarters 1, 2, and 3.

Steps:

Select cell F5.

Insert the following:

=SUM(IF(C6>30000,C6,0),IF(D6>35000,D6,0),IF(E6>50000,E6,0))

Formula Breakdown:

Inside the SUM function, the first IF function, C6>30000 is the logical_test which checks whether the sales that have been sold in the 1st quarter are greater than $30,000 or not. The second IF function checks whether the sales that have been sold in the second quarter are greater than $35,000 or not. The third IF function checks whether the sales that have been sold in the third quarter are greater than $50,000 or not.
The SUM function sums up these quarterly sales.

Press Enter on your keyboard.

AutoFill the function down.

Things to Remember

The #N/A! error occurs when the formula or a function in the formula fails to find the referenced data.
The #DIV/0! error happens when a value is divided by zero (0) or the cell reference is blank.

Download the Practice Workbook

Nested IF and SUM Formula.xlsx

<< Go Back to Nested Formula | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!