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

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.

nested formula in excel if and sum


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.

nested formula in excel if and sum

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

nested formula in excel if and sum


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.

nested formula in excel if and sum

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

nested formula in excel if and sum

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

nested formula in excel if and sum


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.

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo