A rent roll lists every unit, its rent, and status. Summing scheduled rent, counting vacancies, and averaging by unit type turns the list into the numbers owners ask for.
The example
Mixed units, occupied and vacant.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Occupied rent | |
| 3 | Total | → $38,400 |
The formula
The formula:
How it works
How it works:
- SUMIF the rent column where status is “Occupied” for scheduled rent.
- COUNTIF vacancies; AVERAGEIF rent by unit type.
- Gross potential rent sums every unit at market, regardless of status.
- Compare actual vs market per unit to find loss-to-lease.
The rent roll is the property’s income statement in one tab. With columns for unit, type, market rent, actual rent, and status, a handful of SUMIF/COUNTIF/AVERAGEIF formulas produce scheduled income, vacancy count, average rent by floor plan, and loss-to-lease — every figure an owner or lender requests. Keep the roll clean and the reporting writes itself.
Try it: interactive demo
Occupied rent and vacant count.
Variations
Vacancy count
Empty units:
Average rent by type
Per floor plan:
Loss-to-lease
Market − actual:
Pitfalls & errors
Status spelling. One consistent label so SUMIF/COUNTIF match.
Scheduled vs collected. Rent roll shows scheduled rent, not cash collected.
Market column. Keep a market-rent column for loss-to-lease.
Practice workbook
Frequently asked questions
How do I total a rent roll in Excel?
How do I find loss-to-lease?
What's the difference from collected rent?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class