✦Template Printable Ideas Insights

Calculate Running Sum in Google Sheets: Step-by-Step Guide

By

Ever found yourself hurrying to calculate running totals in Google Sheets for forecasting sales, tracking profits, or tallying votes? A running sum, simply put, is a cumulative sum of numbers, where each new total includes the previous total plus the new number. Here's a step-by-step guide on how to do a running sum in Google Sheets, along with some neat tricks to enhance your productivity.

Before we dive in, ensure you have Google Sheets open and have some numerical data ready. For this guide, let's assume you have a list of daily sales figures starting from A2 down to A10:

Basic Running Sum Calculation

Let's first tackle the fundamental process of calculating running sums.

How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Google Sheets
How to Create a Cumulative Sum Chart in Google Sheets
How to Create a Cumulative Sum Chart in Google Sheets
Do you think Grand Sum using ArrayFormula in Google Sheets is the right way to do it?
Do you think Grand Sum using ArrayFormula in Google Sheets is the right way to do it?

Believe it or not, Google Sheets' auto-sum feature can handle running sums with minimal effort. Here's how:

1. AutoSum Tool

Position your cursor in the cell where you want the running total to start. In our case, this is B2. Then, click on the 'Insert' menu in the toolbar above, and select 'Functions' > 'AutoSum'. This should automatically calculate the sum of all values in the sales column up to and including today's sales (cell A2).

How to Add and Sum in Google Sheets
How to Add and Sum in Google Sheets
Creating a tracker in google sheets
Creating a tracker in google sheets
Math in Google Sheets: Add, Sum, Subtract, Multiply, Divide, Square
Math in Google Sheets: Add, Sum, Subtract, Multiply, Divide, Square

But what if you want to include tomorrow's sales as well? This is where theRunning Sum formula comes into play.

2. Running Sum Formula

Now, let's manually create a running sum formula. Assuming your sales data starts from A2, enter this formula in B2:

`=SUM($A$1:B2)`

the google sheets has hidden gems you're not using on your webpages
the google sheets has hidden gems you're not using on your webpages
a computer screen with a message on it that says pov you don't know how to use google sheets
a computer screen with a message on it that says pov you don't know how to use google sheets
6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
Google Sheets Guide: SUMIFS Function
Google Sheets Guide: SUMIFS Function
How to Create Progress Bars in Google Sheets (Step-by-Step)
How to Create Progress Bars in Google Sheets (Step-by-Step)
Weekly Planner (Google Sheets)
Weekly Planner (Google Sheets)

Breaking this down, `SUM($A$1:B2)` tells Google Sheets to add all values from the range A1 to B2. The `$` signs are essential – they anchor the range, ensuring future runs only add incoming data and don't add values all over again.

To drag this formula across to include tomorrow's sales, simply copy B2 and paste it into B3. You'll notice B3 now includes today's and tomorrow's sales. Copy and paste this pattern down to B10 to complete your running total.

Dynamic Array Formula for Running Sums

Since the introduction of dynamic array formulas in Google Sheets, the process of creating running sums has become more straightforward and less error-prone. Let's explore this new method.

Google Sheets Checklists
Google Sheets Checklists
Google Sheets Formula Cheat Sheet
Google Sheets Formula Cheat Sheet
How to Sum a Column in Google Sheets: 4 Easy Methods
How to Sum a Column in Google Sheets: 4 Easy Methods

Assuming your sales data remains in column A with the first entry in A2, enter this formula in B2:

`=SUM(FILTER(A:A, A:A<>""))`

What's happening here? `FILTER(A:A, A:A<>"")` filters out empty cells in column A, and `SUM()` adds up all the remaining values. Copy this formula into B3 and drag it down to B10 for your running sums.

1. AutomaticEXTRACT Unique Values

While the dynamic array formula above works wonders, sometimes you might want to extract unique values before running a sum. Perhaps you want to track daily, weekly, or monthly sales. In such cases, use UNIQUE alongside FILTER, like so:

`=SUM(FILTER(TRANSPOSE(UNIQUE(A:A, TRUE, 2, 1))), TRANSPOSE(UNIQUE(A:A, TRUE, 2, 1))<>""))`

This formula extracts unique date values from column A and calculates their corresponding sums.

Remember to replace "(monthly, weekly, or daily)" with the correct aggregation period in the formula above.

2. Grouped Totals

Now, let's compute grouped totals, such as monthly sales. Assuming your sales data and corresponding dates are in columns A and B, use this formula in E2:

`=SUM(FILTER(B:B, (A:A>DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1))*(A:A<=TODAY())))`

This formula calculates the total sales for the current month by filtering dates between the start of the month and today.

Drag this formula down to E13 to derive totals for the 12 previous months. To extract yearly totals, adjust the date criteria in your formula accordingly.

And there you have it! Now you can swiftly calculate running sums, grouped totals, and so much more in Google Sheets using these easy, time-saving techniques. Happy crunching!