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.
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).
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)`
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.
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.