To all my fellow spreadsheet enthusiasts, today I’m going to let you in on some juicy secrets about running total formulas in Google Sheets. Whether you’re a beginner or an advanced user, these formulas will come in handy when you want to calculate cumulative sums in your spreadsheets. So grab your favorite beverage and prepare to be amazed!
Running Total: The Basics
Before we dive into the nitty-gritty, let’s understand what a running total is. In simple terms, it’s the sum of a sequence of numbers that keeps growing as we add more numbers to it. Think of it as keeping track of the total score in a cricket match. Each time a player scores runs, we add them to the total.
Normal Running Total Formulas
Let’s start with the basic formulas for calculating running totals in Google Sheets. Here are two simple methods:
1. Using the Addition Operator
This is the simplest way to calculate a running total. Follow these steps:
- In cell C2, insert the formula
=B2. - In cell C3, insert the formula
=B3+C2and drag it down.
That’s it! Your running total is ready to use.
2. Using the SUM Function
Here’s another approach using the SUM function. This one is a little tricky, but I promise it’s worth it!
You can directly use the following formula in cell C2 and drag it down:
=SUM($B$2:B2)
Notice the use of the dollar symbol ($) in the formula. It makes the first part of the cell reference absolute and the second part relative. After copying the formula down, check the formula in cell C3. It should look like this:
=SUM($B$2:B3)
Now, let’s move on to the more exciting part – array-based running total formulas!
Array-Based Running Total Formulas
In Google Sheets, we can use different functions like SUMIF, MMULT, DSUM, and SCAN to calculate array-based running totals. Let’s explore two popular options:
1. Cumulative Sum Array Formula Using SUMIF
With just one line of code, you can calculate a running total using the SUMIF function. Here’s how:
In cell C2 (make sure C2:C is empty), enter the following formula:
=ArrayFormula(If(len(B2:B),(SUMIF(ROW(B2:B),"<= "&ROW(B2:B),B2:B)),))
Impressive, right? The core part of this formula is the SUMIF function, which takes a range, a criterion, and an optional sum_range. It simply adds up all the numbers in the range up to the specified criterion.
2. Cumulative Sum Array Formula Using MMULT
Now, let’s explore another method using the MMULT function. This formula might be a bit complex, but trust me, it’s worth the effort!
Enter the following formula in cell C2:
=ArrayFormula(MMULT(IF(ROW(B2:B6)>=TRANSPOSE(ROW(B2:B6)),1,0),B2:B6))
You can also make the cell range B2:B6 open by using this formula:
=ArrayFormula(if(len(B2:B),MMULT(IF(ROW(B2:B)>=TRANSPOSE(ROW(B2:B)),1,0),n(B2:B)),))
Please note that this formula might be resource hungry, so use it wisely.
Other Array-Based Running Total Formulas
If you’re feeling adventurous, there are two more options you can explore:
3. Running Total Array Formula Using DSUM
The DSUM function is a powerful database function that can calculate running totals in Google Sheets. Here’s how you can use it:
=ArrayFormula(DSUM(transpose({B2:B6,if(sequence(5,5)^0+sequence(1,5,row(B2)-1)<=row(B2:B6),transpose(B2:B6))}),sequence(rows(B2:B6),1),{if(,,);if(,,)}))
This formula requires formatting the numbers in the range B2:B6 to match a specific pattern within DSUM. It might seem a bit complex, but believe me, it’s worth it!
4. Running Total Array Formula Using SCAN
Last but not least, we have a new entrant in the world of cumulative sum array formulas – SCAN! This LAMBDA helper function allows us to calculate running totals easily. Here’s how:
For a closed range, use the following code:
=scan(0,B2:B6,lambda(a,v,(a+v)))
If you want to open B2:B6 to B2:B, use this formula:
=ArrayFormula(if(B2:B="",,scan(0,B2:B,lambda(a,v,(a+v)))))
And that’s a wrap, my friends! You now know all the secrets to calculating running totals in Google Sheets. I hope you found this article helpful and informative. If you want to explore more amazing tips and tricks, be sure to visit Crawlan.com.
Thanks for joining me on this spreadsheet adventure. Until next time, happy calculating!
*Note: The original article included a list of related topics, which has been removed to focus solely on running total formulas.
