Using COUNTIFS, SUMIFS, and AVERAGEIFS Excel Functions in Real Estate Underwriting (Updated Aug 2024)

Why use COUNTIFS, SUMIFS, and AVERAGEIFS functions in real estate? When real estate analysts first start their careers, they’re often required to work with large data sets and to transpose property and portfolio information from one format to another in Excel. At first, being unfamiliar with many of Excel’s functions, they manually perform these operations. This increases the time it takes to perform tasks and makes it more likely they’ll commit an error.

Over time, these analysts come to discover the powerful functionality of Excel’s tools and functions that save them valuable time and reduce the chance for error. I’d like share three such functions: the COUNTIFS, SUMIFS, and AVERAGEIFS functions and a few ways to use them in real estate underwriting.

When Will I Use the COUNTIFS, SUMIFS, and AVERAGEIFS Functions?

Much of real estate underwriting and analysis requirecccccs working with and making sense of large data sets. You, the CRE professional, must analyze rent rolls with hundreds of tenants, portfolio operating statements with dozens of properties, lease agreement provisions with numerous co-tenancy clauses, and market data with years of vacancy rates and absorption figures.

When assessing these data, oftentimes you’re dealing with multiple variables – perhaps its various submarkets and property types in one data set on the market or various tenant types and buildings in one rent roll. To work with these data, you need a function that allows you to set multiple criteria to complete your calculation.

An Example Related to Real Estate

So when might you use this in practice? Allow me to give a specific example related to real estate. Let’s imagine you’re tasked with calculating the average vacancy rate in 2016 of power centers in a given submarket. You have a large set of data containing vacancy rates of all buildings in an MSA going back ten years. How do you complete this task?

One option, is to manually find all power centers in your submarket, copy the vacancy rates in 2016 for each, and take their average. Easy? Sure, but time consuming. And what if you missed a building when you were going through the long list?

Another option, the quick, efficient, and less error-prone option, is to use the AVERAGEIFS function. Simple plugin type out AVERAGEIFS, assign submarket, property type, and year as criteria, and let Excel do the rest of the work. And on top of that, you can then calculate 2015 and 2014 as an added bonus for your boss!

The Mechanics of Using These Functions in Real Estate

First, Allow me to share an example of using these three functions in real estate analysis. Then, if you need more instruction, I’ve embedded two Microsoft-developed tutorials for using the the COUNTIFS and SUMIFS functions. Note, no video is included for the AVERAGEIFS functions but the logic is nearly identical to the SUMIFS functions.

How to Use the COUNTIFS Function

How to Use the SUMIFS Function

Download the Worksheet Used in Above Example

If you have any questions or comments, please don’t hesitate to reach out.

And for those looking to deepen their understanding and enhance their analytical skills, consider exploring our new Data Analysis GPT for Commercial Real Estate. This tool uses AI technology to help streamline your complex data analysis tasks. Whether it’s enhancing your hold-sell analyses like we discussed in this article or tackling other data-intensive tasks in CRE, the Data Analysis GPT can provide powerful support.