Using COUNTIFS, SUMIFS, and AVERAGEIFS Excel Functions in Real Estate Underwriting (Updated Dec 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.
- Looking for more resources to learn Excel? Take out our free ‘Definitive Guide to Microsoft Excel for Real Estate‘.
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 2025 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 2025 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 2024 and 2023 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 COUNTIFS and SUMIFS functions. Note, no video is included for the AVERAGEIFS function but the logic is nearly identical to the SUMIFS functions.
How to Use the COUNTIFS and SUMIFS Functions
Below find a video created by Microsoft that demonstrates how to use the COUNTIFS and SUMIFS functions.
Download the Completed File Used in this Tutorial
To make this tutorial accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – similar real estate training exercises sell for $25- $100+). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Version Notes
v1.1
- Misc. formatting cleanups
- Added Version tab
v1.0
- Initial release
