In this article, you will read 5 examples relating to using multiple If conditions in Excel for aging.
How to Use Multiple If Conditions in Excel for Aging: 5 Methods
1. Use Multiple If Conditions through Nested IF Functions Based on Aging
In this example, I will use the Nested IF function to assign different teams based on aging.
❶ First of all, insert the following formula in cell D5.
=IF(C5<20,"A",IF(C5<40,"B",IF(C5<60,"C","D")))❷ Then hit the ENTER button.
❸ After that, drag the Fill Handle icon from cell D5 to D10.
Now you will see all the teams have been assigned based on aging as in the picture below:
Formula Breakdown
➤ IF(C5<60,”C”,”D”)
Here the IF function checks whether the value in C5 is less than 60. If yes, it returns C. If no, it returns D.
➤ IF(C5<40,”B”,IF(C5<60,”C”,”D”))
Here the IF function checks whether the value in C5 is less than 40. If yes, it returns B. If no, it goes to IF(C5<60,”C”,”D”).
➤ IF(C5<20,”A”,IF(C5<40,”B”,IF(C5<60,”C”,”D”)))
Here the IF function checks whether the value in C5 is less than 20. If yes, it returns B. If no, it goes to IF(C5<40,”B”,IF(C5<60,”C”,”D”)).
2. Multiple If Conditions for Aging by Using IF, AND, & TODAY Functions
Here, I will use the IF, AND, & TODAY functions to calculate overdue dates against a deadline.
❶ First, insert the following formula in cell D5.
=IF(AND(C5-TODAY()<=60,C5-TODAY()>=0),B5,"")❷ Then press ENTER.
❸ Now drag the Fill Handle icon from cell D5 to D10.
Finally, you will see the projects having a due date of 0 to 60 days in the column, 0-60 Days.
Formula Breakdown
➤ C5-TODAY()<=60
This part of the code determines whether the date in C5 and today’s date differ less than or equal 60 days or not.
➤ C5-TODAY()>=0
This part of the code determines whether the date in C5 and today’s date differ less than or equal 0 or not.
➤ AND(C5-TODAY()<=60,C5-TODAY()>=0)
Here, the AND function ensures both C5-TODAY()<=60 and C5-TODAY()>=0 becomes true.
➤ IF(AND(C5-TODAY()<=60,C5-TODAY()>=0),B5,””)
If AND(C5-TODAY()<=60,C5-TODAY()>=0) becomes true, the IF function returns data from B5 or it leaves a blank.
❹ Again, insert the following formula in cell E5.
=IF(C5-TODAY()>60,B5,"")❺ Now hit ENTER.
❻ Drag the Fill Handle icon from cell E5 to E10.
Finally, you will see the projects having a due date of more than 60 days in the column, > 60 Days.
Formula Breakdown
➤ C5-TODAY()>60
This part of the code determines whether the date in C5 and today’s dates differ by more than 60 days or not.
➤ IF(C5-TODAY()>60,B5,””)
If C5-TODAY()>60 becomes true, the IF function returns data from B5 or it leaves a blank.
3. Use Combined Functions to Enable Multiple If Conditions for Aging in Excel
Here, I will use the IF, ISTEXT, ISBLANK, & TODAY functions to calculate days from two dates.
I will write a formula to subtract the starting date from the ending date. If the ending date is missing the formula will subtract the starting date from today’s date.
❶ At first, insert the following formula in cell E5.
=IF(ISTEXT(C5),"",IF(ISBLANK(D5),TODAY()-C5,D5-C5))❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell E5 to E10.
Finally, you will get the ages in days in the Age in Days column.
Formula Breakdown
➤ ISTEXT(C5)
The ISTEXT function checks whether cell C5 contains any text value.
➤ ISBLANK(D5)
The ISBLANK function makes sure cell D5 is not blank.
➤ IF(ISBLANK(D5),TODAY()-C5,D5-C5)
If cell D5 is blank then the IF function activates TODAY()-C5. Or, it activates D5-C5.
➤ IF(ISTEXT(C5),””,IF(ISBLANK(D5),TODAY()-C5,D5-C5))
If cell C5 doesn’t contain any text then the first IF function activates IF(ISBLANK(D5),TODAY()-C5,D5-C5).
4. Multiple If Conditions to Categorize Age with IFS Function
Here, I will use the IFS function to assign teams based on aging.
❶ First, insert the following formula in cell D5.
=IFS(C5<20,"A",C5<40,"B",C5<60,"C",C5>60,"D")❷ Then press the ENTER button.
❸ Now drag the Fill Handle icon from cell D5 to D10.
Now you will see all the teams have been assigned based on aging as in the picture below:
Formula Breakdown
➤ C5<20,”A”
If the age in cell C5 is less than 20, then the IFS function assigns team, A.
➤ C5<40,”B”
If the age in cell C5 is less than 40, then the IFS function assigns team, B.
➤ C5<60,”C”
If the age in cell C5 is less than 60, then the IFS function assigns team, C.
➤ C5>60,”D”
If the age in cell C5 is More than 60, then the IFS function assigns team D.
5. Estimate Total Salary Based on Multiple If Conditions Relating to Aging
Now, I will use the SUMIFS function to calculate the total salary.
Here, I will do the calculation based on multiple conditions.
The first condition is the job type has to be a Desk job.
The second condition is the age has to be between 25 and 45.
❶ Now insert the following formula in cell C16.
=SUMIFS(C:C,D:D, "Desk",B:B, ">=25",B:B, "<=45")❷ Then hit the ENTER button.
In cell C16, you will get the total salary for the age range 25 to 45 with job type Desk.
Formula Breakdown
➤ C:C
This refers to the entire column C that the summation operation will be based on in column C.
➤ D:D
This means the first condition is in column D.
➤ “Desk”
This is the first condition that has to be met.
➤ “>=25”
First limit of the second condition.
➤ “<=45”
Second limit of the second condition.
➤ B:B
Cell range to apply the second condition.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 5 methods to use multiple If conditions for aging in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
<< Go Back to Ageing | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
