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 formula in Excel IF and **SUM** effectively with appropriate illustrations.

## Download Practice Workbook

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

## 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**”.

## 2 Ways to Use Nested IF and SUM Formula in Excel

Let’s assume we have an **Excel **large worksheet that contains the information about several **sales representatives** of **Armani Group**. The name 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 with IF and AND Functions in Excel**

### 2. IF Function Nested in SUM Function

Last but not the least, we will nest **the IF function** inside **the SUM function**. Obviously, this is an easy and time-saving task also. 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 the sales that have been sold in the 1st quarter are greater than**$30,000**or not. The second**IF**function, check the sales that have been sold in the second quarter is greater than**$35,000**or not. The third**IF**function, check the sales that have been sold in the third quarter is 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.

**Read More: How to Use Nested IF Function in Excel (6 Ideal Examples)**

## 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.

## Conclusion

I hope all of the suitable methods mentioned above that nested formula 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.