When working with commercial real estate data sets, you may need to create a sum for a range of values that meet a certain condition. Luckily, Microsoft Excel has a built-in function called “SUMIF” that makes this task easy.
This article defines the SUMIF function, explains the syntax for using it, and provides an example of using it in a commercial real estate scenario.
What is the SUMIF function?
SUMIF is a Microsoft Excel function that allows users to sum a range of values only if a certain set of conditions are met. In terms of CRE analysis, this function is especially useful when performing financial analysis on data sets provided by brokers or property owners.
SUMIF Syntax
Like all functions in Excel, to use SUMIF properly, users must write the formula using a specific syntax.
SUMIF Required syntax: SUMIF(range, condition, [sum_range])
To understand how this works, it helps to break the function down into components.
SUMIF is the name of the function. Whenever you use the SUMIF function, it starts with an equal sign (=) followed by the name of the function so that Excel knows which function you are using. This argument is required.
Range refers to the range of numbers to sum. For example, for commercial real estate, this could be the output from a rental ledger or sales report. This argument is required.
Criterion is the condition for which you want to sum the values. Depending on the context, it can be a number, a cell reference, text, or another function. This argument is required.
SumRange refers to the range of cells to be added only if it is different from the defined range, so this argument is optional.
An example is useful to show how these arguments work.
SUMIF Examples
There are countless ways to use the SUMIF function in real estate financial modeling, but one of the most common ways is illustrated in the example below.
Let's say a commercial real estate analyst receives a data export for a property they are conducting due diligence on, which includes a bank ledger with a month's worth of expense details, a portion of which is shown in the following worksheet.
Column A of this table contains expense category descriptions. Column B lists the amounts. The analyst's job is to add up the costs in each category to more accurately estimate the expenses for the pro forma line items. To show how this works using the SUMIF function, we'll use the “Landscaping” category as an example. To sum the “Landscaping” expense line items, we'd write the SUMIF formula as follows:
Landscaping cost = SUMIF(A3:A16,”Landscaping”,B3:B16)
Using the above syntax, variables in an expression look like this:
Range: A3:A16 is the “range” that contains the sum criteria (expense categories). Criteria: Since the task is to sum the landscaping line items, the word “landscaping” is the criteria. Since this is a word and not a cell, it needs to be in quotation marks. Sum Range: Since the sum range is different from the criteria range, it needs to be entered as part of the formula, which is B3:B16.
Combining these arguments, this formula tells Excel to sum all of the values associated with the word “Landscaping” in the specified range, resulting in $3,807.
This same formula can be repeated for each expense category by replacing the word “landscaping” with the other expense category. The correct amounts for each category are shown in the table below.
Expense Category Amount Landscaping $3,807 Property Taxes $3,981 Utilities $3,445 Maintenance $4,913 Total: $16,146
In this scenario, using the SUMIF function is a quicker and easier way to complete this task than summing each row individually.
Common mistakes
Although the SUMIF function is easy to use, there are several common mistakes that users can make that can lead to errors and wrong answers. The three most common mistakes are:
Formatting: If your criteria ranges and/or total ranges are not formatted properly, they can lead to inaccurate results. For example, one of the most common mistakes is having extra spaces after certain values. In the example above, if there was an extra space after one of the “Landscaping” entries, that space would not be included in the total. There should be no extra spaces in your data table. Quotation marks: Words/text should be in quotation marks. In the example above, if the word “landscaping” was not in quotation marks, you would not get the correct answer. Criteria Ranges / Total Ranges: It is important for real estate professionals to distinguish between criteria ranges and total ranges when appropriate. In the example above, the line item descriptions are in column A, so this is the total criteria. The line item values are in column B, so you need to specify these separately in your formula. If both the criteria and values are in one column, there is no need to separate them.
For these reasons, users should take special care when writing SUMIF formulas to avoid errors and incorrect answers.
Variations of SUMIF
SUMIF is a powerful function that can be used as the “base” for two other useful functions: SUMIFS and SUMPRODUCT.
SUMIFS
The SUMIFS function is used when there are multiple summation conditions. This function has the following syntax:
Syntax: =SUMIFS(sum_range, criteria_range1, criteria_range1, [criteria_range2, criteria2]…)
Note that the arguments in the SUMIFS function are listed in a different order than in the SUMIF function (the sum range first, the criteria range second), so it can be easy to confuse the two.
To illustrate how the SUMIFS function works, let's expand the above example to include another set of criteria. Let's say that each line item expense also includes the month it was incurred. The data is summarized in the following table.
Now, suppose the analyst wants to sum only the landscaping expenses incurred in July. Using the above syntax, the formula can be written as follows:
=SUMIFS(B2:B12,A2:A12,”Landscaping”,C2:C12,”July”)
To understand these arguments, it is helpful to break them down separately.
Total Range: The total range is B2:B12 and represents the amounts to be totaled. Criteria Range 1: The analyst wants to total the costs of landscaping, so the first criteria range is A2:A12, where the category descriptions are listed. Criteria 1: The analyst wants to total the costs of landscaping, so the first criteria is “Landscaping”. Note that this is text and therefore needs to be in quotation marks. Criteria Range 2: The second criteria is only the landscaping costs for July. Therefore, the second criteria range is C2:C12. This range includes the months in which the costs were incurred. Criteria 2: Finally, the analyst needs to specify that she is only looking for landscaping costs incurred in July. Therefore, the second criteria is “July”. This also needs to be in quotation marks.
The result of this formula is $2,807. For more information on the SUMIFS function, check out the official Microsoft Excel support page here.
SUMPRODUCT
The SUMPRODUCT function allows you to sum or multiply two ranges of values at the same time. In a real estate financial model, there is one scenario where this is particularly useful: rent rolls. The syntax for the SUMPRODUCT function is:
Syntax: =SUMPRODUCT(Array1, [array2], [array3]…)
To illustrate how this works, consider the following basic rent roll:
Suppose an analyst needs to calculate the total annual rental income from this rental ledger. There are multiple ways to do this, but the fastest and easiest way is to use the SUMPRODUCT function. The “arrays” represent the columns of data to be summed and multiplied. Column B is the number of units, and column C is the monthly rent. Therefore, the total annual rental income is calculated by summing and multiplying these two columns and multiplying the result by 12. The formula looks like this:
Annual income = SUMPRODUCT (B2:B5,C2:C5)*12
Writing the formula this way allows the analyst to calculate the total in one go rather than having to use multiple formulas to calculate the total in multiple steps. For more information on the SUMPRODUCT function, see the Microsoft Excel support page here.
Summary and conclusion
SUMIF is a conditional Microsoft Excel function that allows you to sum a range of values that meet a certain condition. It is especially useful when performing commercial real estate investment or cash flow analysis.
The syntax for writing the formula is “SUMIF(range, condition, [sum_range]), where range is the cells containing the condition, condition is the condition you want to sum the values for, and sum range is the values you want to sum, but only if this range is different from the condition range.
The SUMIF function has two useful variations that are also useful in the context of real estate finance: the SUMIFS function, which allows you to sum a range of values based on multiple criteria, and the SUMPRODUCT function, which allows you to simultaneously sum and multiply a range of values.