How to Use QUERY in Google Sheets for Finance

Enter these headers and illustrative values in Transactions!A1:E8:

Format column A as dates and column D as USD. Formatting alone doesn’t convert text into dates or numbers, so confirm that the underlying values use the correct data types.

These examples use an exclusive end date. A reporting window that begins January 1 and ends March 1 includes January and February but excludes March 1.

Enter this formula in Report!D1:

=QUERY(
  Transactions!A1:E8,
  "select A,C,D",
  1
)

This formula returns columns A, C, and D in that order. Its final argument, 1, tells QUERY that the source contains one header row.

When the source is a direct sheet range, the query uses the worksheet’s column identifiers. For example, a source range of B:E still uses B, C, D, and E rather than rebasing those columns to A:D.

Expected output in Report!D1:F8:

Your spreadsheet locale may display the dates differently, but the underlying values should match this output.

A where clause applies conditions to individual rows. Text literals need single quotation marks, numbers don’t, and dates use the query language’s date-literal syntax.

A text condition looks like this:

where E = 'Posted'

For a numeric condition:

where D >= 1000

For an inclusive-start, exclusive-end date range:

where A >= date '2026-01-01'
  and A < date '2026-03-01'

You can combine all 3 conditions with and:

where E = 'Posted'
  and D >= 1000
  and A >= date '2026-01-01'
  and A < date '2026-03-01'

Here, the start condition includes the January 1 row, while the end condition excludes the March 1 row.

When combining and with or, use parentheses to make the intended logic explicit. The following condition includes posted Rent or Food transactions:

where E = 'Posted'
  and (C = 'Rent' or C = 'Food')

For the sample data, the matching rows are the January 1 Rent transaction and the Food transactions dated January 31, February 1, and February 28.

Next, create these controls on the Report tab:

Enter this control formula in Report!B5:

=AND(
  ISNUMBER(B1),
  ISNUMBER(B2),
  B2>B1,
  ISNUMBER(B4),
  OR(
    EXACT(B3,"Posted"),
    EXACT(B3,"Pending")
  )
)

The expected result is TRUE. This control confirms that:

• Both dates are stored numerically.
• The exclusive end date follows the start date.
• The minimum amount is numeric, including zero.
• Status is exactly Posted or Pending, including capitalization.

It detects invalid report inputs but doesn’t convert or repair them.

Now enter the detail-report formula in Report!H1:

=IF(
  NOT(B5),
  "Check inputs",
  QUERY(
    Transactions!A1:E8,
    "select A,C,D"&
    " where E = '"&B3&"'"&
    " and D >= "&
      SUBSTITUTE(TEXT(B4,"0.############"),",",".")&
    " and A >= date '"&TEXT(B1,"yyyy-mm-dd")&"'"&
    " and A < date '"&TEXT(B2,"yyyy-mm-dd")&"'"&
    " order by D desc"&
    " limit 3",
    1
  )
)

This formula:

• Inserts the selected status into the query.
• Formats dates as yyyy-mm-dd.
• Serializes a decimal minimum with the period required by the query language.
• Sorts amounts from largest to smallest.
• Limits the report to 3 data rows.

Expected output in Report!H1:J4:

Each returned row has Posted status, an amount of at least $1,000, and a date within the selected reporting window.

To build a category summary, enter this formula in Report!L1:

=IF(
  NOT(B5),
  "Check inputs",
  QUERY(
    Transactions!A1:E8,
    "select C,sum(D),count(D),avg(D)"&
    " where E = '"&B3&"'"&
    " and A >= date '"&TEXT(B1,"yyyy-mm-dd")&"'"&
    " and A < date '"&TEXT(B2,"yyyy-mm-dd")&"'"&
    " group by C"&
    " order by sum(D) desc"&
    " limit 5"&
    " label C 'Category',"&
      " sum(D) 'Total',"&
      " count(D) 'Count',"&
      " avg(D) 'Average'"&
    " format sum(D) '$#,##0.00',"&
      " avg(D) '$#,##0.00'",
    1
  )
)

Each QUERY clause has a distinct role:

• group by C creates one row per category.
• sum(D) calculates the total amount.
• count(D) counts non-null numeric amounts.
• avg(D) calculates the average of those amounts.
• order by sum(D) desc sorts categories by total.
• label replaces the generated headings.
• format displays totals and averages as currency.

This summary doesn’t apply the minimum amount from Report!B4. That threshold belongs only to the detail report, although both reports still use the same input-validation control.

Expected output in Report!L1:O4:

Reconcile the result to the source:

• Income: $3,000 + $3,100 = $6,100
• Rent: $1,200
• Food: $0 + $80 = $80
• Grand total: $6,100 + $1,200 + $80 = $7,380

Because the blank Food amount is null, it isn’t included in the count or average. Zero, however, is a non-null numeric value. Food therefore has a count of 2 and an average of $80 ÷ 2 = $40.

The $7,380 grand total is the main control for this workbook. If the grouped output doesn’t reconcile to that amount under the baseline controls, inspect the criteria, data types, date boundaries, and source range.

The following manual test expectations come from the defined inputs and arithmetic. They haven’t been executed here. Run each test independently from the original fixture, or restore every changed cell before beginning the next one.

The baseline is:

• Transactions!A1:E8 contains the 7 source rows from Step 1.
• Report!B1 contains 2026-01-01.
• Report!B2 contains 2026-03-01.
• Report!B3 contains Posted.
• Report!B4 contains the numeric value 1000.
• All formulas use their original source ranges and limits.

Baseline control

Make no changes. Report!B5 should return TRUE, and the summary should reconcile to $7,380.

Zero amount

Inspect Transactions!D4, which contains numeric zero. Food should remain $80, with a count of 2 and an average of $40.

Blank amount

Inspect blank Transactions!D7. The blank shouldn’t increase the numeric count. Food should remain $80 with a count of 2.

Malformed amount

In a separate copy, replace Transactions!D5 with the text value unknown. Because the column is predominantly numeric, QUERY can treat the text as null rather than zero. The expected Food result is $0 total, count 1, and average $0, while the grand total falls to $7,300.

This outcome indicates a source-data problem, not a valid $80 variance. Restore the numeric value 80 before continuing.

Invalid minimum

Replace Report!B4 with the text one thousand. The input control should return FALSE, and both guarded report formulas should display Check inputs.

Blank minimum

Clear Report!B4. The input control should return FALSE, and both guarded formulas should display Check inputs.

Zero minimum

Set Report!B4 to numeric zero and change limit 3 to limit 10 in the detail formula. The input control should remain TRUE.

The expected detail amounts are 3100, 3000, 1200, 80, and 0 in descending order. The blank amount doesn’t satisfy the numeric condition.

Decimal minimum

Set Report!B4 to 79.5 and change the detail limit to 10. The expected amounts are 3100, 3000, 1200, and 80. The constructed query receives 79.5 as the threshold.

Blank or invalid status

Clear Report!B3, enter Approved, or enter lowercase posted as separate tests. The input control should return FALSE in each case because only the exact values Posted and Pending are permitted.

Invalid source status

In an unvalidated test copy, change Transactions!E6 from Posted to Other. The Income summary should fall from $6,100 to $3,000, and the grand total should fall from $7,380 to $4,280. QUERY excludes the altered row because it no longer matches the status criterion.

Invalid dates

Run these as separate tests:

• Clear Report!B1.
• Replace Report!B2 with text.
• Set Report!B1 to 2026-03-01 and Report!B2 to 2026-03-01.
• Set the start date later than the end date.

In each case, the input control should return FALSE, and both reports should display Check inputs. Because the end date is exclusive, it must be later than the start date.

No matching period

Set Report!B1 to 2026-04-01 and Report!B2 to 2026-05-01. The input control should remain TRUE, but no source rows match. Confirm the precise empty-result presentation in your spreadsheet and locale.

Start boundary

Restore the baseline dates. The January 1 Rent row should be included because the start condition uses greater than or equal to.

End boundary

Change Transactions!E8 from Pending to Posted. The summary should still report Rent as $1,200 rather than $2,400 because the March 1 transaction is excluded by the exclusive end date.

Pending period

Set Report!B1 to 2026-03-01, Report!B2 to 2026-04-01, and Report!B3 to Pending. The summary should contain Rent at $1,200, with a count of 1 and an average of $1,200.

Duplicate transaction

Copy Transactions!A5:E5 to A9:E9 and extend the summary source through row 9. Food should become $160, with a count of 3 and an average of $53.33 when displayed to 2 decimal places.

The formula includes the duplicate because no transaction identifier or deduplication rule has been defined.

Opening entry

Enter the following row in Transactions!A9:E9 and extend the summary source through row 9:

The summary should add an Opening balance group with a total of $500, count of 1, and average of $500. QUERY treats it as an ordinary matching row because the source has no field that distinguishes opening entries.

Missing query column

Change a source range to Transactions!A1:D8 while leaving a query condition that refers to column E. Google Sheets should report a query column-reference error. Confirm the exact message in your spreadsheet.

Incorrect header count

Change the final argument in the Step 2 formula from 1 to 2. QUERY should treat the first transaction as part of the header area, leaving 6 transaction rows from January 15 through March 1.

Locale separators

If your spreadsheet requires semicolons between function arguments, replace the relevant formula separators, including those used by TEXT and SUBSTITUTE. Don’t automatically replace punctuation inside the quoted query-language text.

The query has a parse error. Check that text and date literals use single quotation marks inside the query text. The surrounding spreadsheet strings use double quotation marks.

A decimal criterion causes an error. The query language expects a period as the decimal separator. The detail formula converts a locale-specific decimal comma before inserting the threshold.

A date filter returns no rows. Confirm that the source and criteria cells contain date values rather than text. The query’s date literal must follow the yyyy-mm-dd pattern regardless of how the cells display dates.

A referenced column can’t be found. Make sure the column exists in the data range. A query referring to E can’t use a source range that ends at D.

A numeric value disappears. QUERY assigns a primary data type to each column. Values of a minority type can be treated as null. Keep the Amount column numeric and place notes in a separate column.

The result can’t expand. Clear values from the cells where the array result needs to appear.

The headings are wrong. Set the third argument to the actual number of header rows. This example uses 1.

The formula separator is rejected. Change function-argument separators to match the spreadsheet locale. Preserve the query-language syntax inside quotation marks.

Use QUERY when one expression needs to filter, sort, group, aggregate, limit, or relabel data.

When the requirement is narrower, another Google Sheets feature may be a better fit:

• Use FILTER to return rows based on direct Boolean conditions.
• Use SORT when the only task is ordering an existing range.
• Use a pivot table when readers need an interactive, drag-and-drop summary.
• Use QUERY when a report should combine several of these operations in one formula.

QUERY formulas are compact, but their criteria strings require careful quotation, date formatting, and column references.

ModelMonkey can use the active Google Sheets spreadsheet, sheet, and range context to help inspect ranges, identify formula errors, analyze tables, and work with formulas.

For this example, select Transactions!A1:E8, open the ModelMonkey sidebar, and ask:

Create a Google Sheets QUERY formula that groups Posted transactions from
2026-01-01 through the day before 2026-03-01 by Category. Return total,
non-null Amount count, and average Amount, sorted by total descending.

Review the proposed formula and reconcile its output to the source rows. For this fixture, the expected controls are:

• Income: $6,100
• Rent: $1,200
• Food: $80
• Grand total: $7,380

Formula assistance doesn’t establish that a formula or its accounting treatment is correct. The source reconciliation remains the governing check.

For more formula-assistance guidance, see <a href="/blog/google-sheets-ai-formula">Google Sheets AI Formula Tools for FP&A</a>.