The Conditional Weighted Average – SUMPRODUCT with SUMIF

buildings-and-creations-3-1518251Consider this scenario: you have a 50 tenant rent roll, consisting of various tenant types (i.e. small inline, large inline, junior anchor, anchor, etc.), and you want to calculate the weighted average rent for each tenant type. If I asked you, what is the fastest and cleanest way to do this in Excel, what would your answer be?

Most likely, you’d tell me to manually sort the tenants into tenant type groups and then use a SUMPRODUCT & SUM formula to calculate the weighted average rent by tenant size [ = SUMPRODUCT(Tenant Size Column, Tenant Rent Column)/SUM(Tenant Size Column)]. Now, you wouldn’t be wrong with this answer, but there’s a slick way to do this calculation without having to manually sort the tenants into groups or customize the formula for specific tenants.

Expanded SUMPRODUCT Functionality

The SUMPRODUCT function can do a lot more than just multiply arrays. Most of us use it to multiple and then sum two or more arrays – an alternative to using Excel’s array {} syntax – but you may not know that the SUMPRODUCT function can also perform other calculations. For instance, the SUMPRODUCT can also do conditional count, conditional sum and most importantly, conditional sum product calculations (you can learn more about SUMPRODUCT’s expanded functionality here).

Thus, coupling the conditional SUMPRODUCT formula with the conditional SUM formula (via the SUMIF function), we can write conditional weighted average formulas that make our real estate models more dynamic and faster to build.

Conditional SUMPRODUCT Concept

The syntax for writing a conditional SUMPRODUCT formula is as follows:

= SUMPRODUCT(–(Conditional Array = “Condition”),Array1, Array2)

The –() language included as the first element in the formula, tells Excel what values to include in the calculation. Once the condition is specified, Array1 is multiplied by Array 2 and then those values are added together to result in some output.

How to Write Conditional Weighted Average Formulas

Once you’ve mastered the conditional SUMPRODUCT concept, you can combine it with the SUMIF function to calculate the weighted average of only those elements you want. The syntax for writing a conditional weighted average formula is as follows:

= SUMPRODUCT(–(Conditional Array = “Condition”),Array1, Array2)/SUMIF(Conditional Array,”Condition”,Array2)

I’ve recorded a short video that familiarizes you with this concept (see below). In the video, I use a typical scenario in which I’ve been given a basic rent roll in an Excel worksheet and I need to calculate the weighted average of various metrics for certain tenants. Using the conditional weighted average concept, I’m able to do this quickly without having to organize/sort/manipulate the rent roll at all. Below the video, you’ll find a copy of the Excel workbook used in the video together with sample formulas for you to use in your own real estate financial models.

Conditional Weighted Average Example
  • Excel workbook used in the tutorial for how to write conditional weighted average formulas
  • Combines advanced SUMPRODUCT and SUMIF to create dynamic weighted average calculation
  • Great for working with rent rolls

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 Head of Real Estate Investments and member of the founding team at Stablewood Properties. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.