# How to count rows by group with QUERY in Google Sheets

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.

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

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

## 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:F13,"select B, count(A) group by B label count(A) 'Orders'",1)`

Result (spills 6 × 2): Rep, Orders; Ana Ruiz, 2; Kim Ortiz, 3; Lee Chen, 2; Mo Diaz, 2; Raj Patel, 3

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

## Other ways

- `=QUERY(A1:F13,"select B, count(A) group by B order by count(A) desc label count(A) 'Orders'",1)`: 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)`: 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".

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

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/count-rows-by-group-with-query