Home and property

Rent roll with XLOOKUP and SUMIFS for small landlords

Build a landlord rent roll in a sheet: XLOOKUP pulls tenant and agreement details, SUMIFS totals rent received per flat per month from a statement tab.

Editorial illustration for Rent roll with XLOOKUP and SUMIFS for small landlords

Use XLOOKUP to pull each flat's tenant and agreement details from an agreements tab, and SUMIFS to total the rent credited to that flat in a given month from a statement tab. The two together give you a one-row-per-flat roll that shows what was due, what arrived and what is short.

What should the two tabs look like?

Keep the layout plain. A tab called Statement holds one row per bank credit, and a tab called Agreements holds one row per flat. Here are sample columns; adjust the letters to match your own sheet.

TabColumnHolds
StatementADate of credit
StatementBNarration as it appears in the statement
StatementCAmount credited
StatementDFlat code you assign, such as 2B
AgreementsAFlat code
AgreementsBTenant name
AgreementsCMonthly rent
AgreementsDAgreement end date

The flat code in Statement column D is typed by you after reading the narration. The formulas depend on that code being spelt the same way everywhere, so choose short codes and stick to them.

How do you pull tenant details with XLOOKUP?

On a tab called Roll, put the flat code in A2. In B2 you can fetch the tenant name with:

  • =XLOOKUP($A2, Agreements!$A$2:$A$20, Agreements!$B$2:$B$20, "Not found")
  • Monthly rent in C2: =XLOOKUP($A2, Agreements!$A$2:$A$20, Agreements!$C$2:$C$20, 0)
  • Agreement end in D2: =XLOOKUP($A2, Agreements!$A$2:$A$20, Agreements!$D$2:$D$20, "Not found")

The arguments are the value to find, the range to look in, the range to return, and what to show if nothing matches. XLOOKUP needs Excel 365 or Excel 2021, and it is also available in Google Sheets. In older Excel, use INDEX with MATCH instead. If you give XLOOKUP a return range that spans several columns, such as Agreements!$B$2:$D$20, it fills the cells to the right in current versions of Excel and Sheets.

How do you total rent received per flat per month?

Put the first day of a month in a cell, say F1, for example 01-04-2026, and format it as a date. Then in F2 total the credits tagged to the flat in A2 within that month:

  • =SUMIFS(Statement!$C$2:$C$500, Statement!$D$2:$D$500, $A2, Statement!$A$2:$A$500, ">="&F$1, Statement!$A$2:$A$500, "<"&EDATE(F$1,1))

SUMIFS takes the range to add first, then pairs of a range and a condition. This one adds amounts in column C where the flat code matches A2, the date is on or after the first of the month and the date is before the first of the next month. EDATE adds one month to the date in F1. Copy the formula across to G1, H1 and so on, with a new month start in each header cell.

How do you spot a shortfall?

Next to each month, compare received with due. With rent in C2 and received in F2:

  • Gap: =C2-F2
  • Flag: =IF(F2=0,"Nothing received",IF(F2<C2,"Short",IF(F2>C2,"Excess","Paid")))

Say the rent is ₹18,000 and the flat received ₹10,000 on the fourth and ₹8,000 on the twelfth. SUMIFS adds both and reports ₹18,000, so the flag reads Paid, which is correct even though the money came in two parts. A tenant who paid ₹17,000 shows a gap of ₹1,000 and the flag Short.

What goes wrong most often?

  • Dates stored as text, so the date conditions match nothing. Retype one date and see whether it right-aligns.
  • Extra spaces after a flat code, which stop XLOOKUP and SUMIFS from matching. TRIM can clean them.
  • A range that stops short of the newest rows. Fixed ranges such as $A$2:$A$500 are easy to read, but extend them when the statement grows.
  • Rent paid for a month in a different month. Tag by the month it is for and add a column if needed.

When does a sheet stop being enough?

Typing flat codes against every credit is the slow part. If you have a lot of statement rows each month, SpendixAI lets you upload the statement as PDF, CSV or Excel and review the rows before saving, and you can download reports as CSV to paste into this sheet. The XLOOKUP and SUMIFS roll above remains useful for the per-flat view.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports