Using SUMPRODUCT and SUMIF to do quick analysis of an apartment unit mix

Listen to this post if you prefer

Analyzing apartment unit mixes is a common task for those underwriting multi-family assets. The unit mix summary table reports on individual apartment unit rent roll data on a categorized basis, such as unit type (e.g., studio, 1 bedroom/1 bath). Understanding averages and other aspects of these subcategories of the overall unit pool is key to understanding the operating potential of the asset. While generating a unit mix from a rent roll is best approached using the UNIQUE function, SUMPRODUCT and SUMIF are often used to analyze the data in the summary table.

The SUMPRODUCT function multiplies ranges or arrays and returns the sum of those products. Commonly used to get to a weighted (“blended”) average value such as blended rent or square footage, this function’s name seems to paint a complete picture of its capabilities. However, SUMPRODUCT is far more powerful and versatile than its name suggests, especially when combined with the SUMIF function.

SUMPRODUCT can interpret multiple criteria for different arrays in its calculation, which makes it incredibly useful for comparing data across two or more arrays. With an understanding of SUMPRODUCT’s criteria checking capabilities, you will see that it can do far more than just multiply two ranges together and sum the products.

Let’s start with a description of SUMPRODUCT’s most basic use. You can follow along in the Excel file here.

Basic use of SUMPRODUCT

Assume you are looking at an apartment building unit mix and you would like to arrive at the total rent/month for all units. SUMPRODUCT is the obvious choice here.

The syntax for SUMPRODUCT is as follows:
=SUMPRODUCT (array1, [array2], …)

Here is the function in action calculating the total monthly rent:

Here, the formula takes the values in column C and multiplies each one by its corresponding value in column D. Rents from every unit type are taken into consideration, giving us monthly rent for the whole building, which amounts to $292,300.

Using SUMPRODUCT with one criterion

Now imagine that you would like to see total monthly rent, but only for units with 2 bedrooms. To do this, we can apply a criterion to the SUMPRODUCT formula. The syntax for doing so is as follows:

= SUMPRODUCT( – – (B2:B7=2),C2:C7,D2:D7)

The double-negative here (–) runs a check on the values in column B, only taking the SUMPRODUCT of corresponding values in columns C and D which fit the criterion. Here, of course, that criterion is a bedroom count of 2.

Here is the operation in action, which uses the bedroom count in column B to SUMPRODUCT only the rents and unit counts for unit types with 2 bedrooms (i.e., $2,000 * 40 + $2,250 * 36).

The result is $161,000 of monthly rent for all two-bedroom units.

This same logic can be applied in multiple different ways to compare rents in the building. For example, you could arrive at the weighted average rent/month for all units with two bedrooms using the following formula:

=SUMPRODUCT( – – (B2:B7=2),C2:C7,D2:D7)/SUMIF(B2:B7,2,D2:D7)

The first part of the formula, SUMPRODUCT, calculates the total monthly rent for units with 2 bedrooms, while the second part of the formula uses SUMIF to divide that rent by the # of units with 2 bedrooms.

Here is that formula in action:

The result is a weighted average rent of $2,118 for all units with two bedrooms.

Using SUMPRODUCT with multiple criteria

SUMPRODUCT can evaluate arrays not only against 1 criterion, but against multiple criteria across different arrays.

Imagine now that your unit mix has some units that are renovated, and others that are unrenovated. You would like to determine total monthly rent for only for 2-bedroom, renovated units. The formula to do so is as follows:

=SUMPRODUCT( – – (B2:B9=2),–(C2:C9=”Renovated”),D2:D9,E2:E9)

And here is the formula in action:

The first part of the formula checks for the numerical value 2 in the array B2:B9, while the second part checks for the descriptor “Renovated”. The SUMPRODUCT function will only operate on those values in the arrays D2:D9 and E2:E9 which pass both criteria. We arrive at a total monthly rent of $124,100 for 2-bedroom, renovated units.

Conclusion

In the examples above, we worked with a small data set to illustrate SUMPRODUCT’s functionality. However, on a larger scale with many more unit types, you can see how useful SUMPRODUCT would be in evaluating rents across an apartment property.

Armed with this knowledge, you hopefully see now that SUMPRODUCT is much more than just a simple function that multiplies two arrays and sums the products. Instead, it can be a dynamic, efficient tool for multiplying and summing values across two or more arrays according to specific criteria.