Home / Sheets questions cookbook / Sales by region and channel

Google Sheets · Question cookbook

How to Show Sales by Region (and Channel) in Google Sheets

One row per region, one column per channel, totals on both edges. QUERY's pivot clause builds the grid in one formula.

By Ed · Published November 8, 2026

Twenty-five years starting companies, consulting, owning a multi-million-dollar P&L and leading business intelligence for large enterprises.

The short answer: =QUERY(Orders!A:K, "select D, sum(J) where year(A) = 2026 group by D pivot E order by D", 1). Regions go down, channels go across. In the sample data, the Northeast sold the most in 2026 at $371,339, $207,283 of it through sales reps.

The sample data

	A	B	C	D	E	F	G	H	I	J	K
1	Date	Order ID	Customer	Region	Channel	Product	Category	Qty	Unit price	Revenue	Cost
2	2025-01-01	SO-10001	Tin Roof Café	Northeast	Sales rep	Sip Lids (case of 1,000)	Supplies	6	54	324	198
3	2025-01-01	SO-10001	Tin Roof Café	Northeast	Sales rep	Espresso Roast 5 lb	Coffee	10	68	680	370
4	2025-01-01	SO-10002	Foxglove Café	Northeast	Online store	Sip Lids (case of 1,000)	Supplies	1	54	54	33
5	2025-01-01	SO-10002	Foxglove Café	Northeast	Online store	Cold Brew Concentrate 1 gal	Coffee	8	42	336	152
6	2025-01-01	SO-10003	Ember Café	Midwest	Online store	Sip Lids (case of 1,000)	Supplies	4	54	216	132
…	8,115 more rows

Orders

Sample data: a tab named Orders with 8,120 order lines from January 2025 to October 2026, one row per order line. Revenue is Qty × Unit price; Cost is the line's cost of goods (Qty × unit cost). Download orders.csv and import it (File → Import) to follow along. The business is fictional.

The classic way: QUERY with PIVOT

Column D is Region, E is Channel and J is Revenue. In A1 of a new tab:

=QUERY(Orders!A:K, "select D, sum(J) where year(A) = 2026 group by D pivot E order by D", 1)

group by D makes the rows, pivot E turns every channel into its own column (sorted A to Z), and each cell is that region's revenue through that channel. With one channel per column, the headings are just the channel names.

For a Total column, type Total in E1 and =SUM(B2:D2) in E2, copied down. The Total row under it is =SUM(B2:B5) copied across.

Example on sample data

fx=QUERY(Orders!A:K, "select D, sum(J) where year(A) = 2026 group by D pivot E order by D", 1)

	A	B	C	D	E
1	Region	Online store	Phone & email	Sales rep	Total
2	Midwest	$178,712	$23,587	$68,363	$270,662
3	Northeast	$120,848	$43,208	$207,283	$371,339
4	Southeast	$200,339	$19,521	$97,293	$317,153
5	West	$121,721	$22,579	$192,805	$337,105
6	Total	$621,620	$108,895	$565,744	$1,296,259

SummaryOrders

Computed from the sample CSV above. Cells formatted as currency and percent.

Only need regions? Drop the pivot: =QUERY(Orders!A:K, "select D, sum(J) where year(A) = 2026 group by D order by sum(J) desc label sum(J) 'Revenue'", 1). Or a pivot table with Region in Rows, Channel in Columns and SUM of Revenue in Values, which adds grand totals for you.

Gotchas

Pivot columns move. A new channel adds a column and pushes the Total column one to the right, so =SUM(B2:D2) now misses a channel. Leave a spare column, and check the headings after each import.
Region usually comes from the customer's address. If a customer moves, decide whether their old orders keep the old region before you compare years.
Empty combinations show blank, not 0. A region with no sales through a channel gets an empty cell. Treat it as zero when you chart it.

The one-cell way: =QUIRIZ

With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload orders.csv as a dataset, or connect your Shopify store. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:

=QUIRIZ("2026 revenue by region with one column per sales channel, plus a total column and a total row", "table")

The "table" mode spills the answer down and to the right from that cell, header row first:

Example on sample data

fx=QUIRIZ("2026 revenue by region with one column per sales channel, plus a total column and a total row", "table")

	A	B	C	D	E
1	Region	Online store	Phone & email	Sales rep	Total
2	Midwest	$178,712	$23,587	$68,363	$270,662
3	Northeast	$120,848	$43,208	$207,283	$371,339
4	Southeast	$200,339	$19,521	$97,293	$317,153
5	West	$121,721	$22,579	$192,805	$337,105
6	Total	$621,620	$108,895	$565,744	$1,296,259

Sheet1

Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.

The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.

Ask the question, get the table

Quiriz answers business questions in a Google Sheets cell, from your imported files, scheduled Google Sheets and connected tools. Free to start.

Install for Google Sheets →

Free on the Google Workspace Marketplace. Or try it on sample data first, no signup.

Frequently asked questions

How do I make a cross-tab in Google Sheets?

Use QUERY with a pivot clause: =QUERY(data, "select D, sum(J) group by D pivot E", 1) puts D down the side and one column for each value of E. A pivot table with a field in Rows and another in Columns does the same.

Why did my pivot QUERY add a column?

A new value appeared in the pivot column, for example a new sales channel. Each distinct value gets its own column, sorted alphabetically, so formulas next to the result can shift.

How do I sort regions by total sales?

Without a pivot, use order by sum(J) desc. With a pivot, QUERY can only order by the group column, so sort the finished table instead (Data, Sort range) or use a pivot table and sort by the grand total.