Using Nested IFs, SUMIFs & COUNTIFs in
Functions Tutorial (Advanced)
Free Online Microsoft Excel Tutorial
Excel 2010 Training -
Formulas & Functions
* How to Use Nested IF statements
How to Use SUMIFs in a Function
How to Use COUNTIFs in a Function
IF functions such as IF, COUNTIF and SUMIF in Excel allow you to create formulas based on evaluating an argument as either true or false, but what happens if you need to evaluate multiple arguments in a single function? This is where nested IF statements, COUNTIFs and SUMIFs come in.
Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Using Nested IFs, SUMIFs and COUNTIFs in Functions Test
* How to Use Nested IF statements
IF statements are powerful functions in Excel, allowing you to evaluate whether a condition is true or false and to return a value or perform an action based on the result of the argument. However, more often than not, more than one condition needs to be evaluated, and instead of using several different functions to achieve this, you can use nested IF statements.
Nested IF statements simply mean more than one IF statement within the same function.
Do you still remember what the syntax for a single IF statement is?
- =IF(logical_test, [value_if_true], [value_if_false])
The screenshot below is a typical example of a worksheet that may be used to analyze data:
In the worksheet example scenario above, the values in the Deposit column for each booking is calculated based on the following instructions:
- * If the room is not booked, insert N/A in the Deposit column.
- * If the room is booked for 3 days or less, insert the full amount from the Total column into the Deposit column (i.e. the full amount is payable).
- * If the room is booked for more than 3 days, a 50% deposit is required on booking. Calculate and insert 50% of the amount in the Total column in the Deposit column.
This is calculated in the Deposit column utilizing nested IF statements as follows:
In the above formula, a second IF statement is nested within the Value_If_True argument of the first IF statement. You will note that the nested IF statement is a complete IF statement on its own with Logical_Test, Value_If_True and Value_If_False statements.
The best method to master how to create a nested IF statement, is to create one:
1. In a new worksheet, recreate the simple dataset as per the example in the screenshot below:
In this example (see screenshot above) we wish to calculate each agent’s bonus based on the Commission Earned value (cells B8:B13 above). The percentages applicable to each level of commission is detailed in the table in cells A1:C5 above. For example, we can see that the first agent, Patrick, has earned a commission of $3000, which falls in the category of between $2000 and $8000, which means he earns a 2.5% bonus calculated on his commission.
2. To use nested IF statements to calculate the bonus amounts for all the agents, start by typing the following into cell C8:
This part of the formula evaluates whether the value of cell B8 is greater than 12000 and if TRUE, applies a 10% bonus the amount contained in B8.
4. If the first condition (B8>12000) is not met (i.e. evaluates to FALSE), the formula next evaluates whether the value in cell B8 is greater than 8000. If this condition is TRUE a 6% bonus is applied to the amount contained in B8.
In cell C8 continue the formula:
5. If the second condition also evaluates to FALSE (B8>8000), the formula next evaluates whether the value in cell B8 is greater than $2000. If this condition is TRUE a bonus of 2.5% is applied to cell B8.
In cell C8 continue the formula:
- =IF(B8>12000,B8*10%,IF(B8>8000,B8*6%, IF(B8>2000,B8*2.5%,
6. Finally, if none of the conditions are met, i.e. the Commission Earned is below $2000, the formula needs to insert a zero value into cell C8.
Complete the formula as follows and press Enter to finalize it:
- =IF(B8>12000,B8*10%,IF(B8>8000,B8*6%, IF(B8>2000,B8*2.5%,0)))
7. Copy the formula down to the other cells in the Bonus column (C8:C13). Your results should resemble those in the screenshot below:
Experiment by changing the Commission Earned and Bonus percentages or create your own scenarios for using nested IF statements.
* How to Use SUMIFs in a Function
SUMIF is great for calculations where there is only one criterion, but what happens when you need a formula to evaluate multiple criterions before performing the calculation? To counter this problem, we use SUMIFs.
Remember the syntax for SUMIF?
- =SUMIF(range, criteria, [sum_range])
The range represents the cells containing the values you wish to add together and the criteria is the condition that must be met for a value in the sum range to be included in the sum.
The syntax for the SUMIFs contain multiple ranges and criteria with syntax as follows:
- =SUMIFS(sum_range, criteria_range1, critera1, criteria_range2, criteria2)
The above is the same as the SUMIF function’s arguments, but it permits for 2 or more arguments and ranges to be evaluated instead of only one.
The best way to master the SUMIFs function is to create and practice using a scenario example within Excel.
1. In a new worksheet, recreate the dataset as pictured in the screenshot below:
In the box in cells A1:B5 in the screenshot above, is the Projected Targets for each quarter applicable to all the branches in the Branch column (A8:A17). For example, the projected target for each branch for Q1 is $2,000.
Next to each branch in the Branch column is the actual sales achieved for each quarter. For example, Parktown’s Q1 Sales is $980. This means Parktown’s Q1 Sales is below the Projected Target for Q1 ($2,000)
2. Below the two tables you recreated from the worksheet in the screenshot above, create a table similar to the one in the screenshot below in cells A19:B23:
3. In the Sum Totals of Targets Met table (see screenshot above), we wish to sum the total sales for all branches for each quarter, but only if the following criteria are met:
- * Only include sales for branches which exceed the relevant projected quarterly amount; and
- * Only include sales for branches that are not closing (i.e. has a No in the Closing column (F9:F17).
To do this, we need to use the SUMIFs function.
The formula we will insert in cell B20 (Total Q1 Sales Met) is:
This formula evaluates:
- * Whether the cell range F9:F17 contains the word No; and
- * Whether the cell range B9:B17 is greater than 2000 (which is the Projected Target for Q1 for each branch).
Only the amounts in cells B9:B17 which meet the above criteria, are then included in the SUM.
Using the instructions above, recreate the formula for Total Q2 Sales, Total Q3 Sales and Total Q4 Sales.
Does your calculations resemble those in the screenshot below?
The above is just an example to demonstrate how SUMIFS may be used.
Remember: in SUMIFs the criteria arguments are always placed within quotation marks regardless of whether it is a text string, a symbol or a number.
* How to Use COUNTIFs in a Function
The COUNTIFs function works almost in exactly the same way as SUMIFs. You use COUNTIFs when you wish to count a number of cells that meet specified conditions and criteria.
Remember the syntax for COUNTIF?
- =COUNTIF(range, criteria)
This function counts the cells in a range which meet specific criteria.
The syntax for COUNTIFs is the same as COUNTIF, but can contain 2 or more criteria and ranges:
- =COUNTIFs(criteria_range, criteria1, criteria_range2, criteria2)
To demonstrate how COUNTIFs work, recreate the table in the screenshot below, into the relevant cells (D29:G32) of the worksheet you created for the SUMIFs tutorial above.
In cell D30, we used a COUNTIF statement to count how many branches made target for Q1, i.e. how many of the values contained in the Q1 Sales column exceeds the Q1 Projected Target.
The formula entered into D30 is:
Recreate this formula for Q2, Q3 and Q4 in cells E30:G30 using the Projected Targets’ values of:
- * Q1: 2000;
- * Q2: 3500;
- * Q3: 2500; and
- * Q4: 4000.
Do your totals match up to those for each quarter in the screenshot above?
To evaluate more than one criterion before including a cell in a count, we use COUNTIFs. We wish to count the following:
- * the number of branches that made target and are not closing for each quarter; and
- * the number of branches that made target and are closing for each quarter.
In cell D31, in the screenshot above, enter the following formula:
This formula evaluates whether the amount in B9:B17 (Q1) is greater than 2000 and, secondly, whether cells F9:F17 (Closing) contains the word No, meaning the branch is not closing.
Based on the above, see if you can complete the COUNTIFs for all the other cells in the summary table.
Do your results resemble those in the screenshot below?
The best way to master COUNTIFs, SUMIFs and nested IFs, is to create scenarios for and practice applying the criteria and ranges to different types of data that can be analyzed.
For completions sake, the entire worksheet used in all the tutorials above is visualized in the screenshot below: