How to Create a Summary Table in Google Sheets

Transforming a dense spreadsheet packed with raw data into a clean, actionable report can feel like a tall order. A summary table is your best tool for this, condensing thousands of rows into a clear snapshot that highlights key metrics. This guide will walk you through the most effective ways to create a summary table in Google Sheets, from the simplest click-and-drag method to more powerful formulas.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Summary Table and Why Do You Need One?

A summary table, also known as an aggregate table, takes a large dataset and organizes it into a compact, more digestible format. Instead of looking at individual entries, it groups data by categories and performs calculations like summing values, counting occurrences, or finding averages.

Imagine you have a spreadsheet with sales data from the last quarter. It contains hundreds of lines, each detailing a single transaction with columns for Date, Product, Sales Region, and Revenue.

While this raw data is comprehensive, it's difficult to answer big-picture questions like:

• Which region had the highest sales?
• What was the total revenue for our top-selling product?
• How many transactions did we process in the "North" region?

Scouring the data line by line is inefficient and prone to error. A summary table solves this by answering these questions at a glance, turning complex data into clear insights you can use to make informed decisions.

Method 1: The Easiest Way with a Pivot Table

A pivot table is Google Sheets' most user-friendly tool for summarizing data. It allows you to dynamically reorganize and aggregate your information without writing a single formula. It's the perfect place to start if you're new to data summarization.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Guide to Creating a Pivot Table:

Let's use our sales data example. Here’s a small sample of what our source data looks like:

(Imagine a table with columns: A=Date, B=Sales Region, C=Product, D=Revenue)

1. Select Your Data: Click anywhere inside your dataset. Go to the menu and select Insert > Pivot table.
2. Choose Your Destination: A dialog box will appear. It usually correctly guesses your data range. You can choose to create the pivot table on a "New sheet" (recommended to keep things clean) or an "Existing sheet." Click Create.
3. Build Your Summary: A new sheet will open with the Pivot table editor on the right sidebar. This is where you tell Google Sheets how to structure your summary. The editor has four key sections:

Example: Summarizing Revenue by Region

To see total revenue for each sales region, we just need to drag and drop the fields into the Pivot Table editor.

• Under "Rows," click Add and select Sales Region. You'll see "East," "North," and "West" appear in your table.
• Under "Values," click Add and select Revenue. By default, it will summarize by SUM.

Instantly, you have a clean summary table showing the total revenue generated by each region. It's that simple.

Example: Summarizing Revenue by Product and Region

To add more detail, you can use both Rows and Columns.

• Keep Sales Region in the "Rows" section.
• Under "Columns," click Add and select Product.
• Ensure Revenue is still in the "Values" section, summarized by SUM.

Now your table shows a breakdown of how much each product contributed to sales within each specific region, complete with grand totals.

Method 2: Full Control with SUMIFS and COUNTIFS Formulas

While pivot tables are fast, you might want more control over your summary table's layout and design. Building a table with formulas like SUMIFS and COUNTIFS lets you create a completely custom, static report.

The core idea is to first design the empty shell of your summary table, then use formulas to pull in the aggregated numbers.

The SUMIFS Function Explained

SUMIFS sums values that meet multiple criteria. Its syntax looks scary at first but is quite logical:

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

• sum_range: The range of cells you want to add up (e.g., the Revenue column).
• criteria_range1: The range of cells you want to check against your first condition (e.g., the Sales Region column).
• criterion1: The condition you're looking for (e.g., the specific region "East").

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step: Building a Summary Table with SUMIFS

1. Set Up Your Table Structure: In a blank area of your sheet, create the headers for your summary. For our example, create two columns: "Sales Region" and "Total Revenue." List the unique regions below the "Sales Region" header ("East," "North," "West").
2. Write the Formula: Let's say your summary table is in columns F and G. Your region names start in F2. Your source data is in columns A through D. In cell G2 (next to "East"), you'll write your SUMIFS formula:

=SUMIFS(D:D, B:B, F2)

Let's break that down:

• D:D is our sum_range (the Revenue column we want to add up).
• B:B is our criteria_range1 (the Sales Region column we want to check).
• F2 is our criterion1. The formula looks at cell F2, finds the value "East," and only sums the revenue row if its region is also "East."

Now, you can just click the small blue square on the bottom right of cell G2 and drag the formula down. Google Sheets will automatically adjust F2 to F3, F4, etc., calculating the totals for each region.

Counting with COUNTIFS

What if you want to know the number of sales instead of the total revenue? You use COUNTIFS, which works just like SUMIFS but without the sum_range.

To count the sales in each region, the formula would be:

=COUNTIFS(B:B, F2)

This tells Google Sheets to count every time the value in cell F2 ("East") appears in the Sales Region column (B:B).

Method 3: The Most Powerful Way with the QUERY Formula

If you're ready to level up, the QUERY function is unmatched in its power and flexibility. It uses a language similar to SQL (Structured Query Language) to select, filter, and organize your data all in one step.

With QUERY, you can build your entire summary table with a single formula, including the headers.

The QUERY Function Explained

The basic syntax is:

=QUERY(data, query, [headers])

• data: The entire range of your dataset (e.g., A1:D100).
• query: The command, written in quotes, that tells the function what to do. This is where the magic happens.
• [headers]: An optional number specifying how many header rows you have.

Step-by-Step: Building a Summary with QUERY

Let's recreate our "Revenue by Region" summary table. Find a blank cell and enter this formula:

=QUERY(A:D, "SELECT B, SUM(D) GROUP BY B")

Hit enter, and your complete summary table appears, headers and all. Here's a translation of the query command:

• SELECT B, SUM(D): "Show me column B (Sales Region) and the sum of column D (Revenue)."
• GROUP BY B: "Show me the sum for each unique value in column B."

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Adding Filters with the 'WHERE' Clause

This is where QUERY shines. Let's say you want to summarize revenue by region, but only for the "Alpha" product. You just add a WHERE clause to your query:

=QUERY(A:D, "SELECT B, SUM(D) WHERE C = 'Alpha' GROUP BY B")

The WHERE C = 'Alpha' command tells the formula to only consider rows where the value in column C (Product) is "Alpha" before it does the summarizing. You can combine multiple actions into one compact and highly efficient formula.

Final Thoughts

Learning how to summarize data effectively turns Google Sheets from a simple data repository into a powerful reporting tool. Pivot tables offer a fast, no-formula approach perfect for exploration, while SUMIFS and COUNTIFS provide precise control over layout. For maximum power and efficiency, mastering the QUERY function will completely change the way you analyze information.

While mastering these functions is a great skill, sometimes you just need answers without building tables at all. We created Graphed to streamline this entire process. Instead of working with ranges and formulas, you can connect your Google Sheet and simply ask natural language questions like, "What was our total revenue per region last quarter?" and get an instant summary or chart. It lets you have a conversation with your data, so you can focus less on writing formulas and more on making data-driven decisions.