Cumulative Balance against Each Payment in Google Sheets

Let’s say we want a formula that returns the cumulative balance against each payment in a column in Google Sheets. In that case, we can use two types of formulas.

  1. Enter a simple formula in every row in the range.
  2. A formula in the first row of the range that self expands.

I would prefer the latter one, as it’s more convenient to use.

Whatever your choice, in this Google Sheets tutorial, you will get both the formulas to return cumulative balance against each payment in Google Sheets.

Cumulative Balance against Each Payment – What Does It Mean?

I know I must start this tutorial by answering this question. To explain, Let’s consider the closing balance column in a bank statement.

The format of my credit card statement looks like this.

Cumulative Balance against Each Payment in Google Sheets - Array Formula

You can see transaction dates, ref. no., description, debits, credits, and closing balance columns. The last column reflects the balance due (cumulative debit including opening balance minus cumulative credit) in each row.

That means Column F contains the cumulative balance. We can use my either of the following running balance formula in cell F2 to populate the values in that column.

Array Formula 1 (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 would prefer a simple formula, use the below one in cell F2. It’s a non-array formula. So you should copy this to the rows down.

Non-Array Formula 1:

=sum($D$2:D2)-sum($E$2:E2)

I am not going to the detailed formula explanation since I have already covered the same earlier (please follow the link given a few paragraphs above). But I will surely leave some tips at the end part of this tutorial.

Now back to the topic, i.e., cumulative balance against each payment or credit transaction as per the above example.

I want the closing balance only against credits. I want to remove the shaded values (please refer to the image above) in column F. That’s what I meant by the term cumulative balance against each payment in Google Sheets.

I am writing this tutorial as I could see some people prefer to return the cumulative balance against the credits only. Here are the formulas for the same.

How to Return Cumulative Balance against Each Payment in Google Sheets

Non-Array Formula 2:

If we follow the sample data range in the above example, here is the syntax to use.

=if(len(E2),non_array_formula_1,)

The expression len(E2) within IF tests whether E2 is blank or not. If blank, it returns a null character, else the cumulative balance up to that row.

In line with the above syntax, here is the formula to use in cell F2.

=if(len(E2),sum($D$2:D2)-sum($E$2:E2),)

Drag it down.

Array Formula 2:

Here there is not much changes compared to the above array formula 1.

The LEN in the earlier array formula uses the transaction dates in A2:A to limit the expansion to those rows. Just replace that reference with E2:E (the credit column reference).

The reason for the reference change is we want the cumulative balance against the payments/credits rows only.

So the formula in F2 will be as below.

=ArrayFormula(if(len(E2:E),(SUMIF(ROW(D2:D),"<="&ROW(D2:D),D2:D)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)),))

The above is the array formula that returns cumulative balance against each payment in Google Sheets.

Formula Explanation

The above is a combination of two SUMIF and one Logical IF formula.

The first SUMIF is an array formula that returns the running total (cumulative sum) of the debits column, whereas the second one returns the cumulative sum of the credit column.

Let’s test them.

In standalone use, you must use =ArrayFormula(if(len(A2:A) with the formulas.

SUMIF 1 (Debits Cumulative Total)

=ArrayFormula(if(len(A2:A),SUMIF(ROW(D2:D),"<="&ROW(D2:D),D2:D),))

SUMIF 2 (Credits Cumulative Total)

=ArrayFormula(if(len(A2:A),SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E),))

In both the SUMIF formulas, only the column (debit/credit) references are different.

Cumulative Sum of Debit and Credit Columns - Array Formula

SUMIF 1 (debits total) – SUMIF 2 (credits total) is the cumulative balance (please scroll up and see Array Formula 1).

Since we want cumulative balance against payments only, we have replaced A2:A with E2:E. The final formula follows this syntax.

=ArrayFormula(if(len(E2:E),SUMIF_1 - SUMIF_2,))

That’s all about how to return cumulative balance against each payment in Google Sheets.

Thanks for the stay. Enjoy!

Sample_Sheet_18521

Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.