How to Show Sales by Product Category in Google Sheets
Which categories bring in the money, and what share of the total each one is. One QUERY for the totals, one simple formula for the share.
The short answer: =QUERY(Orders!A:K, "select G, sum(J) where year(A) = 2026 group by G order by sum(J) desc label sum(J) 'Revenue'", 1), then divide each total by the sum for its share. In the sample data, Coffee is 67.2% of 2026 revenue.
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 | ||||||||||
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, then a share column
On a new tab, in A1. Column G is Category and J is Revenue:
=QUERY(Orders!A:K, "select G, sum(J) where year(A) = 2026 group by G order by sum(J) desc label sum(J) 'Revenue'", 1)That spills the categories into A2:A5 and their revenue into B2:B5. Type Share in C1, then in C2, copied down, divide by the total of the column:
=B2/SUM($B$2:$B$5)Format C as a percent. The Total row in row 6 is =SUM(B2:B5) and =SUM(C2:C5), which should read 100%.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Revenue | Share |
| 2 | Coffee | $870,662 | 67.2% |
| 3 | Supplies | $302,208 | 23.3% |
| 4 | Tea | $105,816 | 8.2% |
| 5 | Equipment | $17,573 | 1.4% |
| 6 | Total | $1,296,259 | 100.0% |
Computed from the sample CSV above. Cells formatted as currency and percent.
Pivot table version: Insert → Pivot table, Category in Rows, Revenue in Values twice. Set the second one's Show as to % of grand total and you get the share column without a formula.
Gotchas
- A total row under a QUERY breaks it. If a new category appears, the QUERY needs one more row, finds your Total in the way, and shows
#REF!. Leave a gap, or put totals above the QUERY. - Blank categories. Rows with no category group into an empty row. Add
and G is not nullto the where clause, or fix the source. - Share of what? Dividing by the visible total gives share of these categories. If you filtered some out, the shares no longer describe the whole business.
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("revenue by product category in 2026 with each category's share of total, plus a total row", "table")The "table" mode spills the answer down and to the right from that cell, header row first:
| A | B | C | |
|---|---|---|---|
| 1 | Category | Revenue | Share |
| 2 | Coffee | $870,662 | 67.2% |
| 3 | Supplies | $302,208 | 23.3% |
| 4 | Tea | $105,816 | 8.2% |
| 5 | Equipment | $17,573 | 1.4% |
| 6 | Total | $1,296,259 | 100.0% |
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.