How to Aggregate Data in Google Sheets

Aggregating data is one of the most fundamental tasks you’ll perform in a spreadsheet. It turns long, detailed lists of transactions or events into concise, understandable summaries that help you see the bigger picture. This guide will walk you through a few of the best methods to aggregate data in Google Sheets, from simple functions to powerful, dynamic tools.

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 Data Aggregation, Anyway?

Data aggregation is the process of gathering and summarizing data. Instead of looking at individual records, you're looking at them as a collective group. Think about a simple list of your monthly expenses. Seeing each individual transaction is useful, but what you really want to know is the total amount spent on groceries, gas, or entertainment. That summary is an aggregation.

In a business context, it’s about answering questions like:

• What were our total sales by region last quarter?
• How many new leads did each marketing campaign generate?
• Which product category is our most popular?

Google Sheets provides several powerful ways to answer these questions without any complicated manual calculations.

For our examples, let's use a sample of sales data from an online store. Imagine you have a sheet named 'SalesData' with columns for Order Date (Column A), Region (Column B), Product (Column C), and Sale Amount (Column D).

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

Method 1: Quick and Simple Aggregate Functions

The easiest way to start is with the basic functions built directly into Google Sheets. These are great for getting a single, straightforward number.

The Usual Suspects: SUM, COUNT, AVERAGE

These functions do exactly what their names suggest and are perfect for a quick overview of a single data column.

• SUM: Adds up all the numbers in a range. To find your total revenue, you would use:
• COUNT / COUNTA: COUNT tallies cells that contain numbers, while COUNTA tallies any cell that isn't empty. To count the total number of sales (assuming every sale has a number in the "Sale Amount" column):
• AVERAGE: Calculates the average of a range of numbers. To find your average sale amount:
• MAX / MIN: Finds the highest or lowest value in a range. To find your largest and smallest single sales:

These functions are your workhorses for simple, one-off calculations. However, they can’t break down your data by category on their own. For that, you’ll need to add some conditions.

Method 2: Conditional Aggregation with SUMIFS and COUNTIFS

What if you want to know your total sales, but only for the "East" region? Or maybe you want to count how many "T-Shirt" products were sold in the "North"? This is where conditional formulas like SUMIFS and COUNTIFS come in handy.

The "S" at the end of SUMIFS and COUNTIFS indicates that you can test for multiple conditions. Even if you only have one condition, it’s a good habit to use the "S" versions so you can easily add more criteria later.

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

How SUMIFS Works

The structure for SUMIFS is: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

1. sum_range: The range of cells you want to add up (e.g., your 'Sale Amount' column).
2. criteria_range1: The range of cells you want to check for your first condition (e.g., your 'Region' column).
3. criterion1: The condition you're looking for (e.g., "North").

Let's say you want to calculate the total sales from the "North" region. Your formula would be: =SUMIFS(SalesData!D2:D1000, SalesData!B2:B1000, "North")

Now, let's add another condition. What if you want to find the total sales for the "T-Shirt" product category within the "North" region? You just add another criteria range and criterion: =SUMIFS(SalesData!D2:D1000, SalesData!B2:B1000, "North", SalesData!C2:C1000, "T-Shirts")

How COUNTIFS Works

COUNTIFS is a bit simpler, as it doesn't need a sum range - it just counts rows that meet your criteria. Its structure is: =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

To count the number of sales made in the "North" region, you’d use: =COUNTIFS(SalesData!B2:B1000, "North")

And to count the "T-Shirt" sales specifically from the "North" region? =COUNTIFS(SalesData!B2:B1000, "North", SalesData!C2:C1000, "T-Shirts")

These formulas are fantastic for building dashboards and summary tables because they place a specific, aggregated value into a single cell, updated in real-time as your source data changes.

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

Method 3: Advanced Aggregation with the QUERY Function

The QUERY function is arguably the most powerful and flexible function in Google Sheets. It lets you aggregate, filter, and rearrange data using syntax similar to SQL (Structured Query Language). While its learning curve is a bit steeper, the payoff is huge, allowing you to create entire summary reports with a single formula.

The basic syntax is: =QUERY(data, query, [headers])

You can use QUERY to perform tasks like grouping data, extracting insights from large datasets, or performing calculations on selected columns. For example, to find the total sales per region, you might use: =QUERY(SalesData!A1:D1000, "SELECT B, SUM(D) GROUP BY B", 1)

This will give you a table that shows each region alongside the summed sales totals. The QUERY function can accomplish in one line what might take several steps and additional columns with other methods.

QUERY is particularly useful for those who want to unlock the full potential of data aggregation in Google Sheets, turning them into a dynamic reporting and analysis tool.