How to Use Multiple If Conditions in Excel for Aging (5 Methods)

In this article, you will read 5 examples relating to using multiple If conditions in Excel for aging.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


5 Methods to Use Multiple If Conditions in Excel for Aging

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.

Use Multiple If Conditions through Nested IF Functions Based on Aging

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

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

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

Read More: Aging Formula in Excel Using IF (4 Suitable Examples)


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.

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

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

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

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

Read More: Excel VBA: Combining If with And for Multiple Conditions


Similar Readings


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.

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

❸ Drag the Fill Handle icon from cell E5 to E10.

Finally, 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)

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

Read More: How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)


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.

Multiple If Conditions to Categorize Age with IFS Function

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

Output: Multiple If Conditions to Categorize Age with IFS Function

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

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

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.

Read More: Excel If Statement with Multiple Conditions in Range (3 Suitable Cases)


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.


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. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo