,

Using COUNTIFS, SUMIFS, and AVERAGEIFS Excel Functions in Real Estate Underwriting

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

COUNTIFS, SUMIFS, and AVERAGEIFS Example Worksheet
  • Worksheet used in video on COUNTIFS, SUMIFS, and AVERAGEIFS functions in real estate
  • Contains all formulas and formatting

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

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30+ billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently President and member of the founding team at Stablewood. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.