ARRAYFORMULA + QUERY in Google Sheets for Finance

The general pattern is:

=QUERY(
  ARRAYFORMULA({source_column_1, source_column_2, derived_expression}),
  "select Col2, sum(Col3) where ... group by Col2",
  0
)

While ARRAYFORMULA expands calculations across rows, QUERY handles operations such as date filters, category summaries, and totals. Google documents the syntax of ARRAYFORMULA and QUERY.

The example below calculates gross transaction amounts and returns July 2026 totals by category.

Set up the transaction data

Create two tabs:

• Transactions
• Summary

In Transactions!A1:D1, enter these headers:

Enter the following illustrative data. Net amounts are in USD, and tax rates are numeric values from 0 through 1.

Using DATE ensures that column A contains actual date values instead of text that resembles dates.

Calculate and summarize the data

First, clear Summary!A1:B1000 so the array result has room to expand. Then enter this formula in Summary!A1:

=QUERY(
  ARRAYFORMULA({
    IF(
      ISNUMBER(Transactions!A2:A1000),
      Transactions!A2:A1000,
    ),
    Transactions!B2:B1000,
    IF(
      ISNUMBER(Transactions!C2:C1000)*
      ISNUMBER(Transactions!D2:D1000),
      IF(
        (Transactions!D2:D1000>=0)*
        (Transactions!D2:D1000<=1),
        Transactions!C2:C1000*(1+Transactions!D2:D1000),
      ),
    )
  }),
  "select Col2, sum(Col3)
   where Col1 >= date '2026-07-01'
     and Col1 < date '2026-08-01'
     and Col2 is not null
     and Col3 is not null
   group by Col2
   order by Col2
   label Col2 'Category', sum(Col3) 'Gross total'",
  0
)

This formula assumes a spreadsheet locale that uses commas as function arguments and array-column separators. Other locales may require different separators.

What each constructed column contains

The array inside QUERY has three columns:

• Col1: Valid transaction dates from Transactions!A2:A1000
• Col2: Categories from Transactions!B2:B1000
• Col3: Gross amount calculated as Net amount × (1 + Tax rate)

Here, Col1, Col2, and Col3 refer to positions in the constructed array, not to the original sheet’s column letters.

The final argument is 0 because the constructed array begins at row 2 and doesn’t contain the source header. The label clause creates the result headers.

Verify the expected result

The included July amounts are:

• Rent: 1200 × (1 + 0) = 1200
• Software, July 3: 100 × 1.10 = 110
• Software, July 31: 50 × 1.20 = 60
• Bank fees: 0 × (1 + 0) = 0

The Software total is therefore 110 + 60 = 170.

The August 1 Travel transaction falls outside the July interval. Meanwhile, the other Travel row has the text value bad, so its derived gross amount is blank and the query excludes it.

The expected result in Summary!A1:B4 is:

Use this result as a control check. The displayed category totals should reconcile with the four valid July source rows, including the valid zero-value Bank fees row. Across all included categories, the total is 0 + 1200 + 170 = 1370 USD.

Keep in mind that this is a gross transaction summary, not an expense-classification control. Any qualifying credit, income, transfer, or opening balance would also be included unless the source contains a transaction-type field and the query filters it.

Why the date filter uses an exclusive upper boundary

The query includes dates greater than or equal to July 1 and less than August 1:

Col1 >= date '2026-07-01'
and Col1 < date '2026-08-01'

For the date-only values in this example, that interval includes July 1 and July 31 but excludes August 1.

Date literals in the query string use yyyy-mm-dd syntax. However, the underlying source cells must still contain real date values.

Why the numeric checks matter

The following condition requires both the net amount and tax rate to be numeric:

ISNUMBER(Transactions!C2:C1000)*
ISNUMBER(Transactions!D2:D1000)

The nested condition also requires the tax rate to be between 0 and 1:

(Transactions!D2:D1000>=0)*
(Transactions!D2:D1000<=1)

Invalid inputs produce a blank derived amount instead of being converted to zero. The query then removes them with:

and Col3 is not null

A legitimate numeric zero remains in the result.

These checks don’t classify or approve transactions, identify duplicates, or validate category names. They also accept negative numeric amounts. Apply separate controls when the summary must include only expenses or approved transactions.

Adapting the formula to full columns

After confirming the bounded formula, you can replace:

• A2:A1000 with A2:A
• B2:B1000 with B2:B
• C2:C1000 with C2:C
• D2:D1000 with D2:D

All component ranges must start on the same row and have the same height. Because open-ended ranges can increase the number of cells calculated, test their effect before using them in a larger workbook.

For an introduction to query clauses and aggregation, see How to Use QUERY in Google Sheets for Finance. For conditional array calculations without QUERY, see ARRAYFORMULA IF in Google Sheets: Finance Example.

Checks to run before relying on the summary

The following are suggested validation cases. They don’t indicate that the formula was executed.

Data validation can reduce future entry mistakes, but imported or pasted values should still be checked.

Common ARRAYFORMULA and QUERY errors

The formula reports an array expansion error

Check whether cells below or beside Summary!A1 contain values. The result needs an empty two-column output area.

The array has mismatched row sizes

Every column inside {...} must cover the same rows. For example, don’t combine A2:A1000 with B2:B100.

QUERY can’t find column A or B

When the data argument is a constructed array, use Col1, Col2, and Col3 instead of A, B, and C.

The first transaction is missing

Confirm that the final QUERY argument is 0. Because the formula excludes the source header row, the input array has zero header rows.

Date criteria return no rows

Confirm that the source cells are numeric dates. Formatting text to look like a date doesn’t convert it into a date value.

Categories appear more than once

QUERY groups distinct text values. Differences in spelling, capitalization, or spaces can produce separate groups. Category approval and cleanup are outside the scope of this formula.

Get help adapting the formula with ModelMonkey

ModelMonkey can inspect the active Google Sheets context and help with spreadsheet formulas. For this example, you could use the following prompt:

In Summary!A1, help me adapt this ARRAYFORMULA plus QUERY summary to the Transactions tab, preserving the July 1 inclusive and August 1 exclusive boundaries.

If the workflow presents a preview, review the proposed change before proceeding. Then compare the resulting totals with the source-row calculations above. Formula assistance doesn’t replace the control check.