← All postsHow Do You Create a Quarterly Sales by Territory Report in Google Sheets?

How Do You Create a Quarterly Sales by Territory Report in Google Sheets?

Carlos GarciaCarlos GarciaPublished

How Do You Create a Quarterly Sales by Territory Report in Google Sheets?

Every sales organization eventually needs the same number. Quarterly totals, broken out by territory, on one page. The underlying data almost always exists already, sitting in a CRM export or a list of closed deals.

What arrives, though, is one long flat list. A date column, a territory column, an amount column, and several thousand rows that look nothing like a report.

The gap is not the arithmetic. Summing a column is trivial. The gap is that a quarter is not something a date column knows about, and a territory is not something a flat export groups by. Both have to be created.

This guide covers the build end to end: shaping the source data, the two Google Sheets routes that actually produce the report, the formula fallback for when neither fits, and exactly where ChatGPT helps and where it does not. Every claim about how these tools behave comes from Google's or OpenAI's own documentation.

What Is the Short Answer?

Shape your data into one row per sale with three columns at minimum, a real date, a territory label and an amount. Then use either a pivot table with a pivot date group, or a single QUERY formula that groups by quarter and pivots on territory.

The pivot table is faster to build and easier for a colleague to re-sort afterward. The QUERY formula lives in one cell, recalculates when the source changes, and is the better choice when the report is a finished output rather than a workspace someone will click around in.

ChatGPT's role is narrower than most guides suggest. It is genuinely good at writing the formula and at drafting the commentary that sits above the table. It is not the route to the numbers themselves unless you upload the data to it, which carries its own documented constraints.

Getting the report right is one problem. Getting found by the people searching for what you sell is another. Get a free audit and see where you stand.

Free audit

Where do you stand in AI search?

A manual SEO + AI search audit of your site: how ChatGPT, Gemini, Perplexity and Claude read and cite it today, and what to fix first. Report within 48 hours, no credit card.

Get my free audit

Or have it all done: AI SEO services, $999 once

Why Is a Quarterly Report by Territory Harder Than It Looks?

A quarter is not a field in your data

Your export has a date. It does not have a quarter. Something has to derive one.

Google Sheets does support quarter grouping in pivot tables, but you would struggle to learn that from Google's own help pages. The end-user page on customizing a pivot table says only to "Right-click a cell with date formatted data, then click Create pivot date group," followed by "Choose a date or time period to group by." It never names a single period. The words quarter, month, year and day do not appear on that page at all.

The periods are documented, just not where a spreadsheet user would look. In the Google Sheets API reference, the DateTimeRule object is described as a way to "organize the date-time values in a source data column into buckets," and its type list includes QUARTER, defined as "Group dates by quarter, for example Q1 (which represents Jan-Mar)," and YEAR_QUARTER, defined as "Group dates by year and quarter, for example 2008 Q4."

That split matters in practice. Quarter grouping works, so disregard any article telling you Sheets cannot do it. But the only place Google writes down which periods exist is a developer reference that a sales operations analyst will never open.

A territory is only a grouping if it is spelled consistently

West, west, West Region and a trailing-space version of West are four territories to a spreadsheet and one territory to you. A pivot table will faithfully hand you four rows.

There is a related trap that Google does document directly: "Filter settings keep their list of visible values, even if the source data is removed." Add a new territory to your source data and an existing pivot filter will not pick it up on its own. You have to reopen the filter so the new value appears.

Dates stored as text are the most common silent failure

Google is explicit that a pivot date group needs "a cell with date formatted data." CRM exports frequently supply dates as strings that merely look like dates.

The clearest first-party statement of the consequence is on the QUERY function page. Google says each column of the data range "can only hold boolean, numeric (including date/time types) or string values," and that where a column mixes types the majority type sets the column's type while minority values are treated as null. A column of text dates with a handful of real ones will quietly drop rows.

DATEVALUE is the conversion tool. Google notes it returns "integers that can be used in formulas," and, importantly, that the formats it recognizes "can depend on region and language settings." Google's own suggested diagnostic is to type an example date into an empty cell without quotation marks and see whether Sheets accepts it.

How Do You Build the Report, Step by Step?

Step 1: Shape the source data

One row per sale. At minimum a date column, a territory column and an amount column. Add rep, product or deal stage if you have them, but do not let the shape of the report dictate the shape of the source.

Google's first documented instruction for a pivot table is load-bearing and easy to skip. Select the source cells, and "Each column needs a header." A headerless export fails at the very first step.

Remove empty rows and columns while you are there. OpenAI names exactly that as something to avoid in a spreadsheet you intend to analyze, and the advice holds equally for a pivot table.

Step 2: Prove your dates are dates

Select the date column and look at the alignment. Real dates right-align by default in Sheets. Text left-aligns.

If they are text, convert them in a helper column with =DATEVALUE(A2) and format the result as a date. Do this before you build anything, not after the totals come back wrong.

Step 3: The pivot table route

Google's documented sequence is short. Open the spreadsheet, select the source cells including headers, click Insert, then Pivot table, then open the pivot table sheet if it is not already in front of you.

In the side panel, add your date column under Rows, your territory column under Columns, and your amount column under Values. Google's wording for this is "next to Rows or Columns, click Add, then choose a value."

Now right-click any date cell inside the pivot table, choose Create pivot date group, and pick the quarter option. Quarters run down the side, territories across the top.

Google notes you can "change how your data is listed, sorted, summarized, or filtered" from the same panel. That is where you switch the value from COUNT to SUM if it did not default correctly, which it often does not.

Step 4: The one-formula QUERY route

QUERY runs "a Google Visualization API Query Language query across data," with the syntax QUERY(data, query, [headers]).

The clause order is fixed, and Google's Query Language Reference states it plainly: select, where, group by, pivot, order by, limit, offset, label, format, options. Google adds that all clauses are optional.

Two clauses do the real work. group by "is used to aggregate values across rows" and produces "a single row ... for each distinct combination of values in the group-by clause." pivot "is used to transform distinct values in columns into new columns," and because multiple rows may share the same pivot values, Google notes that "pivot implies aggregation."

The quarter comes from a documented scalar function. quarter() "Returns the quarter from a date or datetime value," and Google flags that "quarters are 1-based, so the function returns 1 for the first quarter, 2 for the second, etc."

With dates in column A, territory in column B and amount in column C, the whole report is one formula: =QUERY(A1:C1000,"select year(A), quarter(A), sum(C) group by year(A), quarter(A) pivot B",1)

One rule from Google's group by documentation explains most of the errors people hit here. Every column listed in select must also appear in group by unless it is aggregated. If your formula is throwing an error, check that first.

There is a trap one function along. month() "Returns the zero-based month value from a date or datetime value." Quarters count from one, months count from zero. Mix the two in a label and your Q1 will be correct while your January comes out as month 0.

Step 5: The SUMIFS fallback

If the report has to live in a fixed grid that other formulas point at, build it with SUMIFS instead. Google's syntax is SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]), and the governing rule is that "To calculate a sum, meet all criteria in a row." That is AND logic, which is exactly what territory-and-quarter needs.

The date side is a window rather than a label. Google's own two-criteria example has precisely that shape: SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<30"). Substitute your quarter start and end dates for those two bounds.

One documented limitation to know before you commit to this route: "Returning sum across multiple columns is not supported." SUMIFS fills one cell at a time, so a four-quarter by six-territory grid means twenty-four formulas.

You will find a more compact SUMPRODUCT technique widely taught, multiplying boolean conditions together. It works. It is worth knowing that Google documents SUMPRODUCT only as the function that "calculates the sum of the products of corresponding entries in 2 equally sized arrays or ranges." The boolean-criteria pattern is a consequence of that definition rather than something Google writes down.

Reports tell you what already happened. Get a free audit to find out what your next quarter's pipeline is missing.

Step 6: Where ChatGPT actually fits

Use it for the formula. "Write me a Google Sheets QUERY that groups column A by year and quarter and pivots column B, summing column C" is exactly the kind of request it handles well, and you can check its answer against the clause order above.

Use it for the words. The report is usually read by someone who wants three sentences on what changed, not a grid of fifty numbers. Paste the finished totals in and ask for the summary.

When Should You Use Each Route?

Use a pivot table when a human will explore the report. It re-sorts, re-filters and re-groups without anyone editing a formula, and the date grouping is a right-click away.

Use QUERY when the report is an output rather than a workspace. One cell, updates when the source does, and nobody can accidentally rearrange it by dragging fields around.

Use SUMIFS when the grid is fixed and other formulas reference specific cells inside it. A pivot table's cells move when the source data changes shape. A SUMIFS grid stays exactly where you put it.

What Are the Limitations Worth Knowing?

Google publishes fewer limits than you would expect

The cell ceiling is real and published: "Up to 20 million cells or 100MB for spreadsheets that are created in or converted to Google Sheets." Note the figure. Twenty million. The ten million number repeated across most spreadsheet articles is not what Google currently publishes.

For pivot tables, the only row limit Google publishes applies to Connected Sheets: "Up to 200k rows for pivot tables in Connected Sheets," alongside "Up to 500k rows or 5m cells for extracts in Connected Sheets."

For an ordinary pivot table built on data inside the spreadsheet, Google publishes no row limit anywhere. Not on the Drive limits page, not on either pivot help page, not in the API reference. The API's PivotGroupLimit field is "The count limit on rows or columns in the pivot group," which is a setting the author chooses rather than a platform ceiling. If an article quotes you a pivot table row cap, it did not come from Google.

Google also publishes no column limit per spreadsheet and no QUERY result-size limit on the pages that would carry them. One conversion limit does exist and is easy to trip with a messy export: "any cell with more than 50,000 characters will be removed in Sheets."

The AI features inside Sheets will not build this report

Sheets has an AI function, documented with the syntax AI("prompt",[optional range]), and Google notes that "You can use either =AI() or =Gemini()." It requires "an eligible Google Workspace or Google AI plan."

Two documented limits rule it out for an aggregate like this one. First, "only the first 350 selected cells with AI functions will be generated." Second, and decisive: "The AI function doesn't have access to your entire spreadsheet or other files in your Google Drive."

Google also says the function "has short term and long term generation limits," that on hitting a long-term limit "you temporarily can't click Generate," and that you can "wait for 24 hours and then try again." It does not publish what either limit actually is.

The Gemini side panel is the better-fitting feature, and Google does list "Create a pivot table" among the things you can ask it for. Two caveats from the same pages are worth carrying: "This feature works best with native Google Sheets files," so convert an .xlsx first, and "The generated chart doesn't link or respond to changes in the original data set that was used to generate it."

ChatGPT's spreadsheet limits are documented, but thin where it counts

The connector exists. OpenAI states that "Google Docs, Sheets, and Slides actions are available through the Google Drive app," that browsing and previewing Drive files "requires read access," and that actions which update a source file "require the corresponding write access."

The limitation most likely to bite a sales operations team is specific and first-party: "Shared Drives aren't included yet." Territory data very often lives in exactly such a drive.

For uploads, OpenAI publishes a hard ceiling, "All files uploaded to a GPT or a ChatGPT conversation have a hard limit of 512MB per file," and then separately, "For CSV files or spreadsheets, the file size cannot exceed approximately 50MB, depending on the size of each row." Note the hedging in that second figure. OpenAI publishes no row-count limit for spreadsheets anywhere.

How the analysis happens is worth knowing, because it changes what you should trust. OpenAI documents that "ChatGPT uses pandas to analyze your data and Matplotlib to create both static and interactive charts with your data." The arithmetic is executed as code rather than reasoned out in prose.

OpenAI also documents the schema step that explains most misread columns: "ChatGPT starts by examining the first few rows of data to understand the schema and types of values which may exist." A text-formatted date in those first few rows can set the type for the whole column. It is the same failure mode as the Sheets side, approached from the other direction.

The absence worth naming

OpenAI publishes no accuracy rate for data analysis. No percentage, no benchmark, no error rate, on any of its data-analysis or accuracy pages.

More pointedly, neither of OpenAI's spreadsheet-analysis help pages carries an accuracy caveat at all. The warnings live on a separate, education-framed page about whether ChatGPT tells the truth, which never mentions uploaded files or spreadsheets. That page does say the model "can't verify facts or do things like complex calculations without access to the Internet or use of plugins," which describes the model without tools rather than the data-analysis feature.

So the instruction to check your totals is not something either vendor hands you. Issue it to yourself. Reconcile the grand total of the finished report against a plain SUM of the source amount column before anybody else sees it.

Four quarters, six territories, and no idea which channel produced any of it? Get a free audit and find out.

Sheets, ChatGPT, or a Proper BI Tool?

For a quarterly sales report by territory, Google Sheets wins on nearly every practical axis, and the reason is unglamorous. The report is small. Four quarters by a dozen territories is fifty numbers.

ChatGPT is a worse place to keep this report than to build it. Output comes back as a file rather than a live tab. OpenAI notes that a downloaded result "will be in a CSV format," and that the execution environment it ran in "is destroyed within 13 hours of the conversation becoming inactive." Nothing persists for next quarter.

A BI tool earns its place at a different threshold: when the same report has to refresh on a schedule, join to other sources, and be read by people who should not see the underlying rows. That is also what Connected Sheets is for, and it is the one configuration where Google actually publishes a pivot table row limit.

The honest sequence for most teams is to build it in Sheets with QUERY, let ChatGPT write the formula and the commentary, and move to a BI tool only when somebody asks for the thing to update itself.

Your reporting is sorted. Your rankings might not be. Get a free audit before your competitors do.

Final Thoughts

This report is not difficult once you see that the only genuinely tricky part is the quarter, and that Sheets handles quarters perfectly well. It simply documents them somewhere no spreadsheet user ever looks.

Shape the data. Prove the dates are dates. Pick the pivot table for exploring or QUERY for publishing. Reconcile the total before you send it. That is the entire job.

ChatGPT is a real help at the two ends of that job, the formula and the write-up, and a poor fit for the middle. If you want more on the write-up half, our guide to how to create a report with ChatGPT covers the prompt patterns that produce something usable instead of something generic.

Everything above about tool behavior comes from Google's Sheets and Drive help pages, Google's Sheets API and Query Language references, and OpenAI's own help center, checked on November 18, 2026.

Want this done for you?

SEO Stuff gets businesses cited by ChatGPT, Gemini, Perplexity and Claude, and ranking in Google. Start with the free audit, a call, or the package.

See where you stand

A free, manual SEO + AI search audit of your site, with a report within 48 hours. No credit card, no sales call.

Get my free audit

Talk it through

A free 15-minute call with the founder about whether AI search is a meaningful opportunity for your business.

Free strategy call

Have it done

The Done-For-You Package: audit, 10 pages of content, 3 DR50+ placements, dashboard. $999 once, delivered in 21 business days.

See the package