While working with Microsoft Excel, sometimes we need to nest formulas. Nesting formulas in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable ways how nested formulas in Excel **IF** and **SUM **effectively with appropriate illustrations.

**Table of Contents**Expand

## Introduction to Nested Formula in Excel

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

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

Where,

- 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 Nested IF and SUM Formula in Excel: 2 Ways

Let’s assume 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. From our dataset, we will nest the **IF **and **SUM **functions. We can easily nest the **IF** and **SUM** functions in Excel. Here’s an overview of the dataset for today’s task.

### 1. SUM Function Nested in IF Function

In this portion, we will nest the **SUM** function inside the **IF** function. Undoubtedly, this is an easy task. From our dataset, we will do it easily. Form our dataset, firstly, we will sum up the sales that have been sold by Ralph in quarters 1, 2, and 3. Secondly, using the **IF **function, we will check whether his sales are Excellent or Good. Let’s follow the instructions below to learn!

**Step 1:**

- First of all, select cell
**F5**.

- After selecting cell
**F5**, type the below**IF**and**SUM**functions in that cell. The functions are,

`=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.

- After typing the formula in
**Formula Bar**, simply press**Enter**on your keyboard. As a result, you will get the output of the**SUM**function that is nested in**IF**The return is “Excellent”.

**Step 2:**

- Hence, autoFill the
**SUM**function that is nested in the**IF**function to the rest of the cells in column**F**.

**Read More:** How to Create a Nested Formula in Excel

### 2. IF Function Nested in SUM Function

Last but not least, we will nest the **IF** function inside the **SUM** function. Obviously, this is an easy and time-saving task. From our dataset, we will do it easily. Form our dataset, firstly, we will use the **IF** function, to sum up the conditional sales that have been sold by Ralph in quarters 1, 2, and 3. Secondly, using the **SUM** function, we will sum up the total conditional sales in quarters 1, 2, and 3. Let’s follow the instructions below to learn!

**Step 1:**

- First of all, select cell
**F5**.

- Hence, type the below
**IF**and**SUM**functions in that cell. The functions are,

`=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.

- After typing the formula in Formula Bar, simply press
**Enter**on your keyboard. As a result, you will get the output of the**IF**function that is nested in the**SUM**The return is $39,825.00.

**Step 2:**

- Further, autoFill the
**IF**function that is nested in the**SUM**function to the rest of the cells in column**F**which has been given in the below screenshot.

## Things to Remember

👉 **#N/A!** error arises when the formula or a function in the formula fails to find the referenced data.

👉 **#DIV/0!** error happens when a value is divided by zero(0) or the cell reference is blank.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

I hope all of the suitable methods mentioned above that nested formulas in Excel **IF** and **SUM** will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

## Related Articles

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