Build your own reports with Montley data

A name for your data

A named range is a name for a group of cells that you can use in a formula instead of an address like Transactions!D2:D. When you move an entire referenced column using Google Sheets’ column controls, the named range follows it, so your formulas can keep using the same name without being rewritten.

A Google Sheets file can contain several tabs, such as Transactions and Accounts. You can add a tab of your own and use formulas to read the data in those existing tabs.

Montley creates these names for you. For example, MONTLEY_V1_TRANSACTIONS_AMOUNT points to the transaction amounts, without the header.

In your connected file, choose Data → Named ranges to see the available names and the cells they point to. You do not need to define them again. The named-range reference explains every supported field.

The names stay independent of your column labels. You can call the Amount column “Value” and still use the same named range. Keep Montley’s range names intact. Deleting a column or copying its values elsewhere does not preserve the connection in the same way as moving the entire column.

Build a monthly money-out report

Start in the same Google Sheets file that Montley syncs to. You need permission to edit it and at least some synced transactions. There is no script to install.

1. Click the + at the bottom to add a tab. Rename it My report.
2. In A1, type Month. In B1, enter =DATE(2026,9,1). This creates September 1, 2026 as a date. Choose the first day of a month covered by your data.
3. In A2, type Currency. In B2, enter USD, or the currency code shown on your Transactions tab.
4. In A5, type Posted money out. Paste this formula into B5.

=SUMIFS(
  MONTLEY_V1_TRANSACTIONS_AMOUNT,
  MONTLEY_V1_TRANSACTIONS_STATUS,"posted",
  MONTLEY_V1_TRANSACTIONS_CURRENCY,B2,
  MONTLEY_V1_TRANSACTIONS_DATE,">="&B1,
  MONTLEY_V1_TRANSACTIONS_DATE,"<"&EDATE(B1,1),
  MONTLEY_V1_TRANSACTIONS_AMOUNT,">0"
)

SUMIFS adds the Amount values only when all the conditions match: the row is posted, its currency matches B2, its transaction date is in the month beginning in B1, and its amount is positive. Changing B1 or B2 changes the report.

Select B1 and use Format → Number → Date if it appears as a number. Format B5 as a number with two decimal places. A currency symbol is only formatting; it does not convert currencies.

Understand what you are adding up

• Positive amounts are money out; negative amounts are money in. A purchase is usually positive. A deposit or refund is usually negative.
• Money out is not automatically spending. Transfers and credit-card payments can also be outflows. To build a spending report, categorize transactions and decide which categories to include. The example above does not subtract refunds.
• Use posted rows for settled activity. Pending amounts may change. Removed rows should not be counted. Reports use Date, which prefers the authorization date, then a known pending date, then the posting date. The hidden Posted Date is available for bank statement reconciliation.
• Keep currencies separate. USD and CAD are different units. Blank currency values are unknown and do not match the example’s USD filter.
• A blank is not always zero. A missing bank description, authorization date, or balance means that value is unavailable. A report also cannot include history that has not been synced.

For a category-specific report, put the exact Category value, such as Food & Drink: Coffee, in B3. Add the following pair before the closing parenthesis of the money-out formula, putting a comma after the previous criterion:

MONTLEY_V1_TRANSACTIONS_CATEGORY_PATH,B3

This uses your selected Category. Plaid Category is a separate suggestion from the provider.

Show the rows behind your total

In My report, enter Date, Description, and Amount in A8, B8, and C8. Paste this formula into A9. Keep the cells below A9:C9 empty so the results have room to expand.

=FILTER(
  HSTACK(
    MONTLEY_V1_TRANSACTIONS_DATE,
    MONTLEY_V1_TRANSACTIONS_DESCRIPTION,
    MONTLEY_V1_TRANSACTIONS_AMOUNT
  ),
  MONTLEY_V1_TRANSACTIONS_STATUS="posted",
  MONTLEY_V1_TRANSACTIONS_CURRENCY=B2,
  MONTLEY_V1_TRANSACTIONS_DATE>=B1,
  MONTLEY_V1_TRANSACTIONS_DATE<EDATE(B1,1),
  MONTLEY_V1_TRANSACTIONS_AMOUNT>0
)

HSTACK puts the three columns next to each other. FILTER returns the rows matching the original money-out formula. Format column A as dates and column C as numbers. The list follows the source row order; that order is not guaranteed to be chronological.

If you added the optional category filter to your total, add MONTLEY_V1_TRANSACTIONS_CATEGORY_PATH=B3 before this formula’s closing parenthesis too, with a comma after the previous condition. This keeps the list and total consistent.

The ranges from one Montley table line up row for row. Keep the columns together when filtering or sorting a report. Ranges from different tables do not line up: use Account ID to match Transactions or Balance History to Accounts.

Named ranges include spare blank rows for future data. These examples exclude them through their conditions. Using ROWS on a whole named range would count its capacity, not the number of transactions.

What if your report is in a separate file?

Named ranges belong to one spreadsheet file. A new tab in your connected file can use them directly. A different Google Sheets file needs IMPORTRANGE.

In the destination file, put the source spreadsheet URL in A1. This example imports its transaction amounts into the cell where you enter the formula:

=IMPORTRANGE(A1,"MONTLEY_V1_TRANSACTIONS_AMOUNT")

You must have access to the source file. On first use, Google may show #REF! and ask you to Allow access. The imported values do not create Montley named ranges in the destination file.

Check who can edit the destination before connecting it: once access is granted, those editors can use IMPORTRANGE to request other parts of the source file too. Importing one range is not a way to restrict them to that range.

For a small report, calculate the summary in the source file and import that summary. Cross-file imports can update later than formulas within the same file. Google recommends limiting imported data and chains of dependent files. Read Google’s IMPORTRANGE guidance.

When something looks wrong

“Unknown range name” or #NAME?

Check the spelling and confirm you are in the connected file. Look under Data → Named ranges. If the name is missing, open that Sheet in Montley and use Doctor to inspect it. Review its proposed repairs. A new optional column can collect future data without recovering its historical values.

“Formula parse error”

Copy the entire formula, including the initial equals sign. These examples use English function names and commas. Depending on your spreadsheet locale, you may need semicolons between arguments or English function names enabled in its settings.

The total is zero, or FILTER returns #N/A

Check B1, B2, and the transaction dates. SUMIFS returns zero when nothing matches; FILTER reports #N/A when no rows match. Confirm that the selected month has posted transactions in that currency.

“Array result was not expanded”

The list needs empty cells below and beside its formula. Move your own notes or other formulas out of the result area.

Your result changes after syncing

Bank data can change as pending transactions post or corrections arrive. Your report reads the current values in the connected file. Keep your formulas in your own tabs, and enter personal categories and notes in their designated columns.

For the complete list of field names, data types, and meanings, open the supported named-range reference.

Google Sheets help: Named ranges, SUMIFS, and FILTER.