How to Use Multiple If Conditions in Excel based on the Age – 5 Methods

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.

Use Multiple If Conditions through Nested IF Functions Based on Aging

  • Drag down the Fill Handle to see the result in the rest of the cells.

Teams were assigned based on age:

Output: Use Multiple If Conditions through Nested IF Functions Based on Aging

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.

Multiple If Conditions for Aging Using IF, AND, & TODAY Functions

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

Output: Multiple If Conditions for Aging Using IF, AND, & TODAY Functions

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.

Use Combined Functions to Enable Multiple If Conditions for Aging in Excel

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

Output: Use Combined Functions to Enable Multiple If Conditions for Aging in Excel

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.

Multiple If Conditions to Categorize Age with IFS Function

  • Drag down the Fill Handle to see the result in the rest of the cells.

Teams were assigned based on the age:

Output: Multiple If Conditions to Categorize Age with IFS Function

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

Use SUMIFS Function to Calculate Total Salary Based on Multiple If Conditions Relating Aging

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!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo