IFS in Excel: A Game Changer for Real Estate Appraisers

If you struggle to manage vast amounts of data efficiently, you’re not alone. But Microsoft Excel’s ‘IFS’ functions act as a powerhouse for your data analysis tasks. To help you sift through your appraisal data more easily and precisely, we’re walking you through how to use IFS in Excel, from breaking down what they are to understanding the individual functions.

What are IFS in Excel?

The traditional IF function has been an Excel staple for a long time, allowing users to perform a logical test and return one value if the test is true and another if it’s false. For instance, =IF(A1 > 10, “high”, “low”) checks if the value in cell A1 is greater than 10, and if so, it returns “high”; otherwise, it returns “low.”

When dealing with multiple conditions, the IF function could get cumbersome because you would have to nest them inside each other, like so: =IF(A1 > 10, “high”, IF(A1 > 5, “medium”, “low”)). This is hard to read and maintain, especially with more conditions.

The IFS function allows you to list several conditions and their corresponding results sequentially. For example, =IFS(A1 > 10, “high”, A1 > 5, “medium”, TRUE, “low”) achieves the same as the nested IF example but is much easier to follow. Each pair of conditions and results is checked in order, and Excel returns the result for the first condition that is true.

Th 

This progression from IF to IFS makes Excel formulas easier to write and read, reducing the likelihood of errors and making spreadsheet management more straightforward, especially for those complex decision-making scenarios!

The different IFS in Excel

Th ‘IFS’ family of functions includes: 

• SUMIFS
• COUNTIFS
• MAXIFS
• MINIFS
• AVERAGEIFS

IFS functions are ideal when handling complex calculations based on multiple criteria, allowing you to perform advanced data analysis with ease.

Imagine you’re tasked with determining the average sale price of homes with over three bedrooms in a specific neighborhood. With traditional Excel functions, you’d have to navigate through nested formulas and IF statements – a process that’s not only time-consuming but also prone to errors. Enter the AVERAGEIFS function, which can give you the information you need with a single, straightforward formula.

These functions can tackle a wide range of real-world scenarios that appraisers face daily. Need to find the maximum sale price of homes in a particular zip code built after a certain year? The MAXIFS function has you covered. Want to count the number of properties with two or more bathrooms that sold for at least a specific price? COUNTIFS is your new best friend.

Now, let’s dive into some practical examples to answer questions related to this practice spreadsheet.
And don’t worry, all the questions are answered at the bottom of the page!

Save this Excel spreadsheet or copy the data into a blank Excel spreadsheet to practice!

IFS in Excel #1: SUMIFS

The SUMIFS function in Excel is used to calculate the sum of values that meet multiple criteria. It allows you to sum up a range of values based on conditions specified in different ranges or columns. The syntax of the SUMIFS function is:

• =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where sum_range is the range of cells to calculate, criteria_range1 is the first range of cells to evaluate against criteria1, and so on. You can have up to 127 range/criteria pairs.

Hands-on exercise: SUMIFS

Using the data in the spreadsheet, answer this question: What is the sum of the total sales prices of all homes with over 2 bathrooms?

The steps:

1. Go to cell I7 and type =SUMIFS(
2. The first range the formula asks for is the range of data you want returned. Here we want the sales prices so, within the parentheses, select the range A2:A15 – be sure to add a comma after the A15!
3. The next range the formula asks for is the first criteria you want to check. We want it to check the number of bathrooms, so you need to select the range from C2:C15 and add a comma after C15.
4. The formula then requires the criteria you want to check the range against. Since we want properties with more than 2 bathrooms, enter “>2”. Close the parentheses and hit enter to see the results in cell I7.

Troubleshooting IFS tip: When you add in a greater or less than criteria, make sure to enclose it in quotation marks as seen in step 4.

What answer did you get?

IFS in Excel #2: COUNTIFS

The COUNTIFS function in Excel is used to count the number of cells that meet multiple criteria. It’s similar to SUMIFS, but it counts the occurrences that fulfill the specified conditions. The syntax is similar:

• =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Hands-on Exercise: COUNTIFS

Using the same spreadsheet, let’s answer this question: How many homes with 2 or more bedrooms sold for at least $400,000[JA1] ?

1. Go to cell I10 and type =COUNTIFS(
2. The first range the formula asks for is the first criteria you want to check. The first criteria to look at is the number of bedrooms so you need to select the range from B2:B15.
3. The formula then requires the criteria you want to check the range against. Since we want homes with 2 or more bedrooms, enter “>=2”.
4. The next range the formula asks for is the second criterion, sales price. Therefore, select the range from A2:A15.
5. The formula then requires the criteria you want to check the range against. Since we want properties that sold for at least $400,000, enter “>=400000”. Once the parentheses are closed and you hit ENTER you will see the results in cell I10.

Troubleshooting IFS tip: Make sure to add commas after  each range or criteria!

What answer did you get?

IFS in Excel #3: MAXIFS

The MAXIFS function is used to find the maximum value in a range that meets multiple criteria. It allows you to identify the highest value based on specified conditions. The syntax for the MAXIFS function is:

• =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Hands-on Exercise: MAXIFS

Use the spreadsheet to answer this question: What is the maximum home sale price of homes in the 78731 zip code built after 1980?

1. Go to cell I8 and type =MAXIFS(
2. The first range the formula asks for is the range of data you want returned. Here we want the sales prices, so select the range from A2:A15.
3. The next range the formula asks for is the first criteria you want to check. The first criteria to look at is the zip code so you need to select the range from D2:D15.
4. The formula then requires the criteria you want to check the range against. Since we want properties in the 78731 zip code, enter 78731.
5. The next range the formula asks for is the second criterion—in this case year built. Therefore, select the range from F2:F15.
6. The formula then requires the criteria you want to check the range against. Since we want properties built after 1980, enter 1980. Once the parentheses are closed and you hit ENTER you will see the results in cell I8.

What answer did you get?

IFS in Excel #4: MINIFS

The MINIFS function works similarly to MAXIFS, but it finds the minimum value that meets specified criteria. The syntax is the same:

• =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Hands-on Exercise: MINIFS:

Use the spreadsheet to answer this question: What is the smallest 3-bedroom home built before 1985?

1. Go to cell I9 and type =MINIFS(
2. The first range the formula asks for is the range of data you want returned. Here we want the size (square feet) so select the range from E2:E15.
3. The next range the formula asks for is the first criteria you want to check. For Question 4, the first criteria to look at is the number of bedrooms so you need to select the range from B2:B15.
4. The formula then requires the criteria you want to check the range against. Since we want 3-bedroom properties, enter 3.
5. The next range the formula asks for is the second criterion, year built. Therefore, select the range from F2:F15.
6. The formula then requires the criteria you want to check the range against. Since we want properties built before 1985, enter “<1985”. Once the parentheses are closed and you hit ENTER you will see the results in cell I9.

What answer did you get?

IFS in Excel #5: AVERAGEIFS

The AVERAGEIFS function calculates the average of values that meet multiple criteria. It allows you to calculate the mean based on specified conditions. The syntax for the AVERAGEIFS function is:

• =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Hands-On Exercise: AVERAGEIFS

Use the spreadsheet to answer this question: What is the average sales price of all homes with over 3 bedrooms?

1. Go to cell I6 and type =AVERAGEIFS(
2. The first range the formula asks for is the range of data you want returned. Here we want the sales prices so select the range from A2:A15.
3. The next range the formula asks for is the first criteria you want to check. We have it to check the number of bedrooms so you need to select the range from B2:B15.
4. The formula then requires the criteria you want to check the range against. It gets a little tricky here because the question asks for properties with over 3 bedrooms. Remember, when you use <, >, etc. you need to put that criterion in quotes. So, enter “>3” to get the homes with greater than 3 bedrooms.

What answer did you get?

Check your answers

Get more resources with our Unlimited Learning Membership

Whether you’re just starting out in your appraisal career or you’re an experienced professional, McKissock has the appraisal CE courses that build your skills while meeting your state requirements. Our Unlimited Learning Membership not only unlocks our entire course catalog, but you’ll also get access to over 400 resources and job aids, including checklists, webinars, articles, and so much more. Sign up today and enjoy the best value in appraisal continuing education!