How to skip blank rows in a QUERY in Google Sheets
Google Sheets formula
=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)Add a not-null test: =QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1) ignores the empty rows of an open range.
- Result on the example
- a 6-row list that spills from H1 in
H1 - Excel
- See below
See it work
The formula is already in H1. Change any input and the result updates, or edit the formula to experiment.
How it works
An open range like A1:F picks up every new row automatically, but it also includes all the empty rows below the data. Grouped together, those make a blank group with a total of 0. where B is not null drops them before grouping, leaving one row per rep.
=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)Result{6 rows × 2 cols} "Rep", "Revenue", "Ana Ruiz", 4750, …
A1:F53 rows × 6 cols: "Date", "Rep", "Region", "Product", …A range: 53 rows × 6 columns.
QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1){6 rows × 2 cols} "Rep", "Revenue", "Ana Ruiz", 4750, …QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.- data
A1:F53 rows × 6 cols: "Date", "Rep", "Region", "Product", … - query
"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'" - headers
1
- data
In Excel
B2:B1000<>"".Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =QUERY(A1:F,"select B, sum(F) group by B label sum(F) 'Revenue'",1) | spills 7 × 2Different result | The same query without the test, to see the problem. An extra blank row with a total of 0 at the bottom. |
| =QUERY(A1:F,"select A, B, F where A is not null",1) | spills 13 × 3Different result | The same test keeps an ungrouped query from returning blank rows. Every order, three columns. |
Common mistakes
- Test a column that is always filled. A row with a blank in the tested column is dropped even if it has data elsewhere.
where B != ''works for text columns, but for numbers and dates use is not null.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Skip blank rows in a QUERY
H1, use QUERY on the open range A1:F to total revenue per rep, without a blank row for the empty rows below the data.Questions
Why use an open range at all?
New rows, such as Google Forms responses, are included without editing the formula.
How do I find the blank rows instead?
Use is null: where B is null and A is not null finds orders with a missing rep.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.