Have you ever needed a formula that returns the cumulative balance against each payment in a column in Google Sheets? Well, you’re in luck! In this tutorial, we’ll explore two types of formulas that can help you achieve just that. Whether you prefer a simple formula or one that self-expands, we’ve got you covered.
What Does Cumulative Balance against Each Payment Mean?
Before we dive into the formulas, let’s clarify what we mean by “cumulative balance against each payment.” Imagine you have a bank statement with columns for transaction dates, reference numbers, descriptions, debits, credits, and closing balances. The closing balance column reflects the cumulative balance in each row, taking into account the opening balance, cumulative debits, and cumulative credits.
Now that we understand the concept, it’s time to explore the formulas.
Formula 1: Array Formula for Cumulative Balance against Each Payment
Our first formula is an array formula that automatically calculates the cumulative balance against each payment. Here’s the formula for cell F2:
=ArrayFormula(if(len(A2:A),(SUMIF(ROW(D2:D),"<="&ROW(D2:D),D2:D)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)),))If you prefer a simple non-array formula, you can use the following formula in cell F2 and copy it down the rows:
=sum($D$2:D2)-sum($E$2:E2)For a detailed explanation of these formulas, please refer to our previous tutorial.
Customizing the Formula for Your Needs
Now, let’s suppose you only want the closing balance against credit transactions and want to remove the shaded values in column F. Here are the formulas you can use:
Formula 2: Non-Array Formula for Cumulative Balance against Credits Only
For this formula, you can use the following syntax in cell F2:
=if(len(E2),sum($D$2:D2)-sum($E$2:E2),)Drag this formula down to apply it to the remaining rows.
Formula 3: Array Formula for Cumulative Balance against Credits Only
If you prefer an array formula, you can modify Formula 1 to only calculate the cumulative balance against credit transactions. Replace the reference to the transaction dates (A2:A) with the credit column reference (E2:E). The formula for cell F2 will then be:
=ArrayFormula(if(len(E2:E),(SUMIF(ROW(D2:D),"<="&ROW(D2:D),D2:D)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)),))And there you have it! These formulas will help you calculate the cumulative balance against each payment in Google Sheets.
Formula Explanation
The formulas mentioned above are a combination of SUMIF and IF functions. The first SUMIF calculates the running total of the debits column, while the second SUMIF calculates the running total of the credits column. The difference between these two sums is the cumulative balance.
To test the individual SUMIF formulas, use the following syntax:
- Debits Cumulative Total:
=ArrayFormula(if(len(A2:A),SUMIF(ROW(D2:D),"<="&ROW(D2:D),D2:D),)) - Credits Cumulative Total:
=ArrayFormula(if(len(A2:A),SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E),))
By subtracting the debits total from the credits total, you’ll obtain the cumulative balance against each payment.
That’s all there is to it! Now you can easily calculate the cumulative balance against each payment in Google Sheets. Enjoy!
