How to count rows by group with QUERY in Google Sheets

Q: How do I keep only groups with more than two rows?

QUERY has no having clause, so query the result: =QUERY(QUERY(A1:F13,"select B, count(A) group by B",1),"select * where Col2 > 2",1).

Q: How do I count and sum in one QUERY?

List both aggregates: select B, count(A), sum(F) group by B.

Q: How do I leave out the header row?

Give every column an empty label: label B '', count(A) ''. When all the labels are empty, QUERY returns no header row.

Google Sheets formula

=QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1)

Group by the column and count another: =QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1) counts the orders for each rep.

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

group by B makes one row per rep, and count(A) counts the non-empty dates in each group, which is the number of orders. Count a column that is always filled; count() skips empty cells.

=QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1)

Result{6 rows × 2 cols} "Rep", "Orders", "Ana Ruiz", 2, …

A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
A range: 13 rows × 6 columns.
QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1){6 rows × 2 cols} "Rep", "Orders", "Ana Ruiz", 2, …
QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.
- dataA1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
- query"select B, count(A) group by B label count(A) 'Orders'"
- headers1

In Excel

Excel has no QUERY function. Pair UNIQUE with COUNTIF: =HSTACK(UNIQUE(B2:B13),COUNTIF(B2:B13,UNIQUE(B2:B13))).

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=QUERY(A1:F13,"select B, count(A) group by B order by count(A) desc label count(A) 'Orders'",1)	spills 6 × 2Different result	Sort by the count to see the busiest reps first. Kim Ortiz and Raj Patel first, with 3 each.
=QUERY(A1:F13,"select B, count(A) where C = 'East' group by B label count(A) 'Orders'",1)	spills 6 × 2Different result	A where clause counts only some rows: here, East orders. Counts East orders only.

Common mistakes

QUERY has no count distinct. To count different products per rep, use COUNTUNIQUE with FILTER instead.
Counting a column with gaps undercounts. count(A) only counts rows where column A has a value.
Text values go in single quotes inside the double-quoted statement, where C = 'East', and QUERY compares them case-sensitively: 'east' doesn't match "East".

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.

Count rows by group with QUERY

In H1, use QUERY to count the orders for each rep, with the count column headed Orders.

Questions

How do I keep only groups with more than two rows?

QUERY has no having clause, so query the result: =QUERY(QUERY(A1:F13,"select B, count(A) group by B",1),"select * where Col2 > 2",1).

How do I count and sum in one QUERY?

List both aggregates: select B, count(A), sum(F) group by B.

How do I leave out the header row?

Give every column an empty label: label B '', count(A) ''. When all the labels are empty, QUERY returns no header row.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.