The Conditional Weighted Average – SUMPRODUCT with SUMIF (Updated Apr 2024)
Consider 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.
Extending SUMPRODUCT for Multiple Conditions using SUMIFS
Now that you’re familiar with using the SUMPRODUCT function to calculate weighted averages based on a single condition, let’s expand this concept to include multiple conditions using the SUMIFS function. This methodology is particularly useful when you need to analyze data subsets that are defined by more than one criterion – for example, calculating the weighted average rent by tenant type and by another factor like lease maturity date or corporate versus non-corporate status.
Expanded Conditional Logic with SUMPRODUCT + SUMIFS
Just as we used SUMPRODUCT for a single condition, we can adapt this function to handle multiple criteria effortlessly. This method avoids the manual sorting or grouping of data and directly answers complex queries within your rent roll or other real estate datasets.
The syntax for writing a SUMPRODUCT + SUMIFS formula that includes multiple conditions is as follows:
= SUMPRODUCT(((ConditionArray1 = “Condition1”) * (ConditionArray2 = “Condition2”)), Array1, Array2) / SUMIFS(Array1, ConditionArray1, “Condition1”, ConditionArray2, “Condition2”)
Here, ConditionArray1 and ConditionArray2 represent different criteria columns in your dataset. For instance, one might be Tenant Type and the other might be whether they are a Corporate Tenant or not.
Practical Application: Dual-Condition Weighted Averages
Imagine you need to calculate the weighted average remaining term for ‘anchor tenants’ which are ‘corporate’ leases. Instead of filtering or sorting the data manually:
- ConditionArray1 (C6:C17) could be ‘Tenant Type’.
- ConditionArray2 (D6:D17) might specify ‘Corporate Status’ as “Yes”.
- Array1 (E6:E17) would represent ‘Tenant Size’ as the weight.
- Array2 (G6:G17) contains the ‘Remaining Term’ values.
The adapted formula would look something like this:
= SUMPRODUCT((($C$6:$C$17 = “Anchor”) * ($D$6:$D$17= “Yes”)),$E$6:$E$17,$G$6:$G$17) / SUMIFS($E$6:$E$17, $C$6:$C$17, “Anchor”, $D$6:$D$17, “Yes”)
Advantages of Multi-Condition SUMPRODUCT + SUMIFS
Using this approach has several advantages:
- No Data Rearrangement: You calculate directly from the raw data without needing to rearrange or sort.
- Dynamic Analysis: Easily adjust conditions to fit different questions or data explorations.
- Efficiency: Reduces the steps and complexity involved in obtaining critical metrics, making your real estate models more dynamic and easier to manage.
In the included file, you’ll find a worksheet titled ‘Example 3 – Two Conditions’ where the dual-condition methodology described above is applied practically. This example will help illustrate the concepts in a real-world scenario, facilitating a better understanding of how to leverage these techniques effectively in your own analyses.
Compatibility
This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365
Download the A.CRE Conditional Weighted Average Calculation Model
To make this model 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 – typical real estate development models sell for $100 – $300+ per license). 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.
We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.
Version Notes
v2.0
- Added two condition logic in Example 3
- Added formula at topic of each example
- Updated placeholder values
- Added ‘Version’ tab
v1.0
- Initial release
