Method 1 – Using Multiple If Conditions through Nested IF Functions Based on the Age
- Enter the following formula in D5.
=IF(C5<20,"A",IF(C5<40,"B",IF(C5<60,"C","D")))- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
Teams were assigned based on age:
Formula Breakdown
IF(C5<60,”C”,”D”)
checks whether the value in C5 is less than 60. If TRUE, it returns C. If False, it returns D.
IF(C5<40,”B”,IF(C5<60,”C”,”D”))
checks whether the value in C5 is less than 40. If TRUE, it returns B. If FALSE, it goes to IF(C5<60,”C”,”D”).
IF(C5<20,”A”,IF(C5<40,”B”,IF(C5<60,”C”,”D”)))
checks whether the value in C5 is less than 20. If TRUE, it returns B. If FALSE, it goes to IF(C5<40,”B”,IF(C5<60,”C”,”D”)).
Method 2 – Using Multiple If Conditions based on the Age with the IF, AND, & TODAY Functions
To calculate overdue dates:
- Enter the following formula in D5.
=IF(AND(C5-TODAY()<=60,C5-TODAY()>=0),B5,"")- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
Projects with a due date between 0 and 60 days are displayed:
Formula Breakdown
C5-TODAY()<=60
determines whether the difference between the date in C5 and the current date is less than or equal to 60 days.
C5-TODAY()>=0
determines whether the difference between the date in C5 and the current date is less than or equal to 0.
AND(C5-TODAY()<=60,C5-TODAY()>=0)
the AND function ensures both C5-TODAY()<=60 and C5-TODAY()>=0 are true.
IF(AND(C5-TODAY()<=60,C5-TODAY()>=0),B5,””)
If AND(C5-TODAY()<=60,C5-TODAY()>=0) becomes true and the IF function returns either the data in B5 or blank.
- Enter the following formula in E5:
=IF(C5-TODAY()>60,B5,"")- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
You will see the projects with a due date greater than 60 days.
Formula Breakdown
C5-TODAY()>60
determines whether the difference between the date in C5 and the current date is greater than 60 days.
IF(C5-TODAY()>60,B5,””)
If C5-TODAY()>60 becomes true and the IF function returns the data in B5 or blank.
Method 3 – Using Combined Functions to Enable Multiple If Conditions based on the Age in Excel
To calculate days between two dates:
- Enter the following formula in D5.
=IF(ISTEXT(C5),"",IF(ISBLANK(D5),TODAY()-C5,D5-C5))- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
You will get the ages in days in the Age in Days column.
Formula Breakdown
ISTEXT(C5)
checks whether C5 contains text value.
ISBLANK(D5)
checks whether D5 is blank.
IF(ISBLANK(D5),TODAY()-C5,D5-C5)
If D5 is blank , the IF function activates TODAY()-C5. Otherwise, it activates D5-C5.
IF(ISTEXT(C5),””,IF(ISBLANK(D5),TODAY()-C5,D5-C5))
If C5 doesn’t contain any text, the first IF function activates IF(ISBLANK(D5),TODAY()-C5,D5-C5).
Method 4 – Using Multiple If Conditions based on the Age with the IFS Function
- Enter the following formula in D5.
=IFS(C5<20,"A",C5<40,"B",C5<60,"C",C5>60,"D")- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
Teams were assigned based on the age:
Formula Breakdown
C5<20,”A”
If the age in C5 is less than 20, the IFS function assigns team A.
C5<40,”B”
If the age in C5 is less than 40, the IFS function assigns team B.
C5<60,”C”
If the age in C5 is less than 60, the IFS function assigns team C.
C5>60,”D”
If the age in C5 is greater than 60, the IFS function assigns team D.
Method 5 – Calculate the Total Salary with Multiple If Conditions based on the Age
The first condition is the job type is a Desk job; the second condition is the age is between 25 and 45.
- Enter the following formula in C16.
=SUMIFS(C:C,D:D, "Desk",B:B, ">=25",B:B, "<=45")- Press ENTER.
In C16, you will see the total salary of Desk employees with an age range 25 to 45.
Formula Breakdown
C:C
refers to the entire column C.
D:D
the first condition is in column D.
“Desk”
the first condition to be met.
“>=25”
the first limit of the second condition.
“<=45”
the second limit of the second condition.
B:B
Cell range to apply the second condition.
Practice Section
Practice here.
Download Practice Workbook
Download the Excel file.
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!
