When working with a commercial real estate dataset, there are situations where it is necessary to create a sum for a set of values, but only if they meet certain criteria. Fortunately, there is an easy way to accomplish this task using a built-in Microsoft Excel function known as “SUMIF”.

This article defines the SUMIF function, describes the syntax for using it, and demonstrates its use in a commercial real estate scenario.

What is the SUMIF function?

SUMIF is a Microsoft Excel function that allows a user to sum a set of values, but only if a certain set of criteria is met. With regard to CRE analysis, this feature is particularly useful when performing financial analysis on datasets provided by a real estate agent or property owner.

SUMIF syntax

Like any function in Excel, proper use of SUMIF requires the user to write the formula using a specific syntax. It is:

SUMIF Required Syntax: SUMIF(range, criteria, [sum_range])

To understand how this works, it’s helpful to break the function down into its components.

SUMIF is the name of the function. All applications of the SUMIF function start with an equal sign (=) followed by the function name. This way Excel knows which function it works with. This argument is obligated.

Range refers to the range of numeric values ​​to add up. In commercial real estate, this can be the output of a rental list or operating statement, for example. This argument is obligated.

Criteria refers to the condition under which values ​​should be summed. Depending on the context, this could be a number, cell reference, text, or some other function. This argument is obligated,

Sum Range refers to the range of cells to be added, but only if it is different from the defined range. As such, this argument optional.

An example is helpful to show how these arguments work.

SUMIF example

There are numerous ways that the SUMIF function can be used in real estate financial modeling, but one of the most common is described in the example below.

Suppose a commercial real estate analyst has received a data export for a property they are conducting due diligence on. It contains a bank ledger with 1 month of line item charges, a selection of which is shown in the worksheet below:

Column A of this table provides a description of the expense category. Column b shows the amount. The analyst’s job is to add up the costs for each category so that they can more accurately estimate the cost of the line items in the pro forma. To demonstrate how this works using the SUMIF function, the “Landscaping” category is used as an example. To sum the “Landscaping” expense line items, the SUMIF formula is written as follows:

Landscape costs = SUMIF(A3:A16,”Landscape Architecture”,B3:B16)

Using the syntax described above, the variables in the equation are as follows:

  • Range: A3:A16 is the “range” that contains the sum criteria – the expense categories.
  • criteria: Since the job is to add up the landscaping line items, the word “Landscaping” is the criterion. Since it is a word and not a cell, it must be enclosed in quotation marks.
  • Sum range: Since the sum range is different from the criteria range, it must be entered as part of the formula and is B3:B16.

When these arguments are combined, this formula tells Excel to sum all the values ​​in the identified range(s) associated with the word “Landscaping”. The result is $3,807.

This same formula can be repeated for each of the expense categories by replacing the word “landscape architecture” with the other expense categories. The correct amounts per category are listed in the table below:

Cost category

Amount of

landscaping

$3,807

Property Tax

$3,981

Utilities

$3,445

Maintenance

$4,913

TOTAL:

$16,146

In this scenario, using the SUMIF function is a faster and easier way to accomplish this task than summing each line individually.

Common mistakes

While the SUMIF function is quite easy to use, there are several common errors that users may encounter that can cause them to come up with an incorrect or incorrect answer. Three of the most common are:

  • layout: If the criteria range and/or sum ranges are not formatted correctly, the result may be incorrect. For example, one of the most common mistakes is having an extra space after certain values. In the above example, if there was an extra space after one of the “Landscaping” items, it would not be included in the sum. There should be no extra spaces in the data table.
  • Quotes: Words/text must be enclosed in quotation marks. In the example above, if the word landscaping were not in quotes, the correct answer would not be given.
  • Criteria Range / Sum Range: It is important for real estate professionals to distinguish between the criteria range and the sum range, if necessary. In the example above, the line item descriptions are in column A, so these are the sum criteria. The values ​​of the line items are in column B, so they must be specified separately in the formula. If both the criteria and the values ​​are in one column, they don’t need to be separated.

For these reasons, users should pay special attention to writing their SUMIF formulas to avoid errors or incorrect answers.

Variations of SUMIF

SUMIF is a powerful function that can be used as a “base” for two other useful functions, SUMIFS and SUMPRODUCT.

SUM

The SUM function is used when there are multiple sum criteria. The syntax for this function is:

Syntax: = SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

It is important to note that the arguments in the SUM function appear in a different order (sum range first, criteria range second) than the SUMIF function. As a result, it can be easy to confuse the two.

To illustrate how the SUMMER function works, the above example has been extended to include another set of criteria. Imagine that each of the line items’ costs also includes the month they were incurred. The data is summarized in the table below:

SUMIF

Now suppose the analyst wanted to add up all the landscaping costs, but only if they were incurred in July. Using the above syntax, the formula could be written as follows:

=SUMIF(B2:B12,A2:A12,”Landscape”,C2:C12,”July”)

To understand each of these arguments, it helps to break them down individually:

  • Sum range: The sum range is B2:B12, which represents the dollar amounts to be added.
  • Criteria range 1: The analyst wants to add up the landscaping expenses, so the first criteria range is A2:A12, where the category descriptions are listed.
  • Criteria 1: Again, the analyst wants to add up landscaping costs, so their first criterion is “Landscaping.” Remember it must be in quotes because it is text.
  • Criteria range 2: The second criterion is for landscape costs, only in July. As such, the second criteria range is C2:C12. This range contains the month in which the release was created.
  • Criteria 2: Finally, the analyst should specify that they are only looking for landscape costs incurred in July. The second criterion is therefore ‘July’. Again, it should be in quotes.

The result of this formula is $2,807. For more information about the SUMIFS function, please visit the official Microsoft Excel support page, which can be found here.

SUM PRODUCT

The SUMPRODUCT function allows a user to add and multiply two sets of values ​​at the same time. In real estate financial models, there is one scenario where this is particularly useful, the rental role. The syntax for the SUMPRODUCT function is:

Syntax: =SUMPRODUCT(array1, [array2], [array3],…)

To illustrate how this works, look at the following bare rental roll.

SUMIF commercial real estate

Suppose the analyst needs to calculate the total annual rental income from this rental list. There are multiple ways to do this, but the fastest and easiest is to use the SUMPRODUCT function. The “arrays” represent the columns of data to be added and multiplied. Column B is the number of units and column C is the monthly rent. So the total annual rental income is calculated by adding and multiplying these two columns, then multiplying the result by 12. The formula looks like this:

Annual Income = SUM PRODUCT (B2:B5,C2:C5)*12

Writing the formula in this way allows the analyst to calculate the total all at once instead of having to do it in several steps, using multiple formulas. For more information about the SUMPRODUCT function, please visit the Microsoft Excel support page, here.

Summary & Conclusion

SUMIF is a conditional Microsoft Excel function that allows users to sum a series of values, but only if they meet certain criteria. It is particularly useful when conducting commercial real estate investments and cash flow analysis.

The syntax used to write the formula is “SUMIF(range, criteria, [sum_range])” where the range is the cells containing the criteria, the criterion is the condition under which the values ​​are to be summed, and the sum range are the values ​​to be summed, but only if this range is different from the criterion range.

There are two useful variants of the SUMIF function that are also useful in a real estate financing context. The first is the SUM function, which allows a user to sum a range of values ​​based on multiple criteria, and the SUMPRODUCT function, which allows a user to sum and multiply a series of values ​​at once.

Source