How to Use Nested IF and COUNTIF Formula in Excel

IF and COUNTIF is two widely used Excel functions. In our day-to-day life, we use them to fulfill our requirements. Moreover, we can also use them in nested form. In this article, we are going to demonstrate to you 3 ideal examples of using nested IF and COUNTIF formulas in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


3 Ideal Examples to Use Nested IF and COUNTIF Formula in Excel

To demonstrate the examples, we will consider 3 different datasets. In every dataset, we are going to apply the nested IF and COUNTIF formulas.

๐Ÿ“š Note:

All the operations of this article are accomplished by using Microsoft Office 365 application.


1. Using Nested Formula for Grace Marking

In the first example, we will use a dataset of 10 students and their examination marks. Our dataset is in the range of cells B5:C14.

Dataset for the first example

We have some conditions in the range of cells B17:D18, and if the marks of the students satisfy the conditions, we will give them an additional mark using the nested IF and COUNTIF formula. We will show the result in column D.

The steps to complete this example are given below:

๐Ÿ“Œ Steps:

  • First of all, select cell D5.
  • Now, write down the following formula in the cell.

=IF(COUNTIF($B$17:$D$18,C5),C5+1,C5)

  • Press Enter.

Using Nested IF and COUNTIF Formula for Grace Marking

  • Then, drag the Fill Handle icon to copy the formula up to cell D14.

  • You will see that an additional mark will be added for marks 69, 79, and 89.

Final Result after Using Nested IF and COUNTIF Formula for Grace Marking

Thus, we can say that our formula works perfectly, and we are able to use the nested IF and COUNTIF formulas in Excel.

๐Ÿ”Ž Breakdown of the formula

We are breaking down the formula for cell D6.

๐Ÿ‘‰ COUNTIF($B$17:$D$18,C6): The COUNTIF function checks for the value in cell C6. If the value is available in the Condition dataset, the function will return 1. Otherwise, it will return 0. Here, the function returns 1.

๐Ÿ‘‰ IF(COUNTIF($B$17:$D$18,C6),C6+1,C6): The IF function checks the result of the COUNTIF function. If the result is 1, it will add a mark to the existing value. On the other hand, it will return the existing value of the dataset. Here, the function returns 80.

Read More: COUNTIF Excel Example (22 Examples)


Similar Readings


2. Applying Nested Formula for Bonus Paying

In the following example, we are going to consider a dataset of 10 employees of a company and their income for a month. So, our dataset is in the range of cells B5:C14.

Dataset for the second example

We have some criteria in the range of cells B17:D17, and we are going to pay an extra $500 bonus to that employee whose salaries are within the condition using the nested IF and COUNTIF formula. The result will be displayed in column D.

The steps to finish this example are given as follows:

๐Ÿ“Œ Steps:

  • At first, select cell D5.
  • After that, write down the following formula in the cell.

=IF(COUNTIF($B$17:$D$17,">="&C5),C5+500,"")

  • Then, press Enter.

Applying Nested IF and COUNTIF Formula for Bonus Paying

  • Now, drag the Fill Handle icon to copy the formula up to cell D14.

  • You will notice that whose salaries are within our condition, a bonus will be added for that employee.

Final Result after Applying IF and COUNTIF Nested Formula for Bonus Paying

Hence, we can say that our formula works effectively, and you can able to use the nested IF and COUNTIF formulas in Excel for extra bonuses paying.

๐Ÿ”Ž Breakdown of the formula

We are breaking down the formula for cell D6.

๐Ÿ‘‰ COUNTIF($B$17:$D$17,โ€>=โ€&C6): The COUNTIF function checks whether the value of the cell C6 is less than our conditions. If the value is less than the criteria value, the function will return 1. On the other hand, it will return 0. Here, the function returns 1.

๐Ÿ‘‰ IF(COUNTIF($B$17:$D$17,โ€>=โ€&C6),C6+500,โ€โ€): The IF function checks the result of the COUNTIF function. If the result is 1, it will add $500 to the existing value. In contrast, it will return the Blank. Here, the function returnsย  $3,121.46.

Read More: How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)


3. Use of Nested Formula for Taking Less Leave

In the last example, we will consider our previous dataset of that 10 employees and their total leave days. We displayed the dataset in the range of cells B5:C14.

Dataset for third example

Now, we want to pay an extra $200 bonus to that employee who didnโ€™t take vacation more than 5 days. For that, the IF and COUNTIF function will help us. These criteria are in the range of cells B17:D18 and the final result will be in column D.

The procedure of this example is explained below step-by-step:

๐Ÿ“Œ Steps:

  • First, select cell D5.
  • Afterward, write down the following formula in the cell.

=IF(COUNTIF($B$17:$D$18,C5),200,"")

  • Press the Enter.

Use of Nested IF and COUNTIF Formula for Taking Less Leave

  • Next, drag the Fill Handle icon to copy the formula up to cell D14.

  • You will figure out that those employeesโ€™ vacation days are within our criteria, the $200 amount is shown on their name row.

Final Result of Using the Nested IF and COUNTIF Formula for Taking Less Leave

Finally, we can say that our formula works successfully, and the nested IF and COUNTIF formula in Excel is helping us to find the employees who deserve the bonus.

ย ย ย 

๐Ÿ”Ž Breakdown of the formula

We are breaking down the formula for cell D8.

๐Ÿ‘‰ COUNTIF($B$17:$D$18,C8): The COUNTIF function checks for the value in cell C8. If the value is available in the Condition dataset, the function will return 1. Conversely, it will return 0. Here, the function returns 1.

๐Ÿ‘‰ IF(COUNTIF($B$17:$D$18,C8),200,โ€โ€): The IF function checks the result of the COUNTIF function. If the result is 1, it will show $200 in cell D8. Otherwise, it will return the Blank. Here, the function returnsย  $200.

Read More: Excel COUNTIF with Greater Than and Less Than Criteria


Conclusion

Thatโ€™s the end of this article. I hope that this article will be helpful for you and you will be able to use the nested IF and COUNTIF formulas in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Donโ€™t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo