Mia W.
Senior Writer
October 6, 2026
5 min
read
You do not need 50 Excel formulas to manage rental properties. A small set of well-designed formulas can handle a surprising amount of landlord reporting.
The key is to build formulas around a consistent table structure.
For the examples below, assume you have the following tables.
Properties
Payments
Expenses
Maintenance
If payment amounts are in cells F2, use:
=SUM(F2:F500)
This gives you total payments. Use the same approach for total expenses.
The formula is simple, but the underlying rule matters: calculate totals from transaction rows instead of typing the total manually.
If Property ID is in column B and Amount is in column F, use:
=SUMIF(B:B,"P001",F:F)
This answers a straightforward question:
How much money was recorded for P001?
You can also use SUMIF to calculate expenses for a particular category:
=SUMIF(C:C,"Repairs",F:F)
This adds the amounts in column F for rows where column C contains “Repairs.”
Suppose you want to calculate repair expenses for property P001.
=SUMIFS(F:F,B:B,"P001",C:C,"Repairs")
The calculation requires both conditions to be met:
This is useful for dashboards because most real landlord questions involve more than one condition.
For example, you might want to see maintenance spending for a particular property during a specific month.
To count open maintenance requests for P001, assuming Property ID is in column B and Status is in column D, use:
=COUNTIFS(Maintenance!B:B,"P001",Maintenance!D:D,"Open")
This can produce a dashboard metric such as:
4 open maintenance requests
You can use the same function to count:
The criteria must match the values in your source data. For example, “Open” and “In Progress” should be treated as separate statuses unless you explicitly include both.
Suppose another sheet contains Property ID in column A and Property Name in column B.
If cell B2 contains P001, use:
=XLOOKUP(B2,Properties!A:A,Properties!B:B,"Not found")
The formula returns the property name associated with that ID.
Microsoft documents XLOOKUP as a function for searching a range and returning the corresponding result. It is available in current Excel versions, including Microsoft 365 and Excel 2024.
You can combine these functions to create a simple property-management dashboard.
Using an Excel Table named Payments:
=SUM(Payments[Amount])
Using an Excel Table named Expenses:
=SUMIFS(Expenses[Amount],Expenses[Property ID],"P001",Expenses[Category],"Repairs")
Using an Excel Table named Maintenance:
=COUNTIFS(Maintenance[Property ID],"P001",Maintenance[Status],"Open")
Using an Excel Table named Properties:
=XLOOKUP(A2,Properties[Property ID],Properties[Property Name],"Not found")
These examples assume your tables use the specified names and column headers. Structured table references work in Excel; Google Sheets generally uses sheet and cell references instead.
The formulas themselves are not complicated. The important part is the data structure underneath them.
If one record says P001 and another says P-001, your SUMIF formula will not know that they represent the same property.
Use consistent IDs.
Likewise, avoid manually typing categories across hundreds of rows. Use dropdowns or controlled values to keep labels consistent.
A sophisticated formula cannot repair inconsistent source data.
The best rental property spreadsheet is not necessarily the one with the most formulas. It is the one that produces reliable answers from clean, consistently structured records.
Microsoft — SUMIFS Function
https://support.microsoft.com/en-us/excel/functions/sumifs-function
Microsoft — XLOOKUP Function
https://support.microsoft.com/en-us/excel/functions/xlookup-function
Google Docs Editors Help — COUNTIFS Function
https://support.google.com/docs/answer/3256550
