# How to skip blank rows in a QUERY in Google Sheets

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.

**Google Sheets:** `=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)`

**Excel:** Excel has no QUERY function. With FILTER, drop the empty rows with a test such as `B2:B1000<>""`.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Product | Units | Revenue |
| 2026-07-03 | Ana Ruiz | East | Widget | 40 | 2080 |
| 2026-07-06 | Raj Patel | West | Gadget | 25 | 2225 |
| 2026-07-10 | Lee Chen | East | Gizmo | 12 | 1740 |
| 2026-07-14 | Ana Ruiz | North | Gadget | 30 | 2670 |
| 2026-07-17 | Kim Ortiz | West | Widget | 55 | 2860 |
| 2026-07-21 | Raj Patel | East | Widget Pro | 18 | 1422 |
| 2026-07-24 | Lee Chen | South | Gadget | 22 | 1958 |
| 2026-07-29 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| 2026-08-04 | Mo Diaz | North | Gizmo | 14 | 2030 |
| 2026-08-07 | Raj Patel | South | Widget | 20 | 1040 |
| 2026-08-12 | Kim Ortiz | North | Gadget | 16 | 1424 |
| 2026-08-18 | Mo Diaz | East | Gizmo Mini | 28 | 1092 |

Formula in H1: `=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)`

Result (spills 6 × 2): Rep, Revenue; Ana Ruiz, $4,750; Kim Ortiz, $5,589; Lee Chen, $3,698; Mo Diaz, $3,122; Raj Patel, $4,687

## 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.

## Other ways

- `=QUERY(A1:F,"select B, sum(F) group by B label sum(F) 'Revenue'",1)`: 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)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/skip-blank-rows-in-query