0

I am trying to figure out an array formula to calculate a running balance within the categories listed in another column.

Here is an example sheet for reference https://docs.google.com/spreadsheets/d/1kmitZF6YtARAi2EB0NggbIhKgsb7tLCknsKBTEKycrw/edit?usp=sharing

The requirements are:

  • Running balance to be calculated on Amount column
  • Running balance should be calculated based on the Category column. I.e. Amounts under category A should only count towards the running balance of A
  • The running balance should be calculated based on values sorted by the ascending Date column. Even though entries may appear in the sheet unsorted.

Any help would be much appreciated!!

Pierce
  • 81
  • 6
  • I am not sure how this running balance should look like (I am not an english native speaker). Could you please use standard formulas to illustrate an example. – Krzysztof Dołęgowski Jan 06 '21 at 13:27
  • Do you mean the running balance should increment just by the amount in column C, ignoring the date, meaning it is always increasing? That is certainly eaasier than if you want to take the date into account. Could you clarify by showing what values you expect in column D, given your current data? – kirkg13 Jan 06 '21 at 14:00
  • I have added an expected value column to illustrate. Basically If I was to do this in python I would first sort by date, then group by category, and do a running balance within each group. Then map the values back to original positions. – Pierce Jan 06 '21 at 21:36
  • Another option might be to use array formula + a script. But I have no experience writing those. – Pierce Jan 07 '21 at 02:08
  • I found this almost identical question: https://webapps.stackexchange.com/questions/87492/running-total-in-google-sheets-with-array The best answer there does it both with a script or a non array copy down function. Both work but the copy down function performs much better it seems. A function that works fast would be Ideal but this is the best found so far. – Pierce Jan 08 '21 at 04:06

3 Answers3

1

Shortest way

Use SUMIFS function to get the sum of a range depending on multiple criteria. In your case you have two:

  • Calculation based on the category
  • Calculation based on the date

Result: cell = sumifs(C$2:C,B$2:B,"="&B2,A$2:A,"<="&A2)

Syntax explained

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

  • sum_range - The range to be summed.

  • criteria_range1 - The range to check against criterion1.

  • criterion1 - The pattern or test to apply to criteria_range1.

  • criteria_range2, criterion2, ... - [ OPTIONAL ] - Additional ranges and criteria to check.

Edit

If you need to fill automatically the new rows you can use the Spreadsheet Service for Google Apps Script and Simple Triggers.

There is a trigger called onEdit(e) that runs automatically when a user changes the value of any cell in a spreadsheet.

You can use the following code to update the Running balance column each time that a value is changed.

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var last_row = sh.getLastRow()
  console.log(last_row)
  var formula = '=sumifs(C2:C,B2:B,B2,A2:A,"<="&A2)'
  sh.getRange('D2:D'+last_row).setFormula(formula)
}

However, this code update the whole column overwriting the formula for the old entries. If you want to update only the new entries in order to have more efficient code, you can use the following code.

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var last_row = sh.getLastRow()
  var last_formula_row = sh.getRange('D1:D').getValues().filter(String).length
  var formula = '=sumifs(C$2:C,B$2:B,B' + last_formula_row + ',A$2:A,"<="&A'+ last_formula_row + ')'
  sh.getRange('D'+last_formula_row+':D'+last_row).setFormula(formula)
}

I attach you both because the second one can be hard to understand without getting the first one.

Reference

  • SUMIFS: Returns the sum of a range depending on multiple criteria.
  • Spreadsheet Service: allows scripts to create, access, and modify Google Sheets files.
  • Google Apps Script: scripting platform developed by Google for light-weight application development in the Google Workspace platform
  • Simple Triggers: run a function automatically when a certain event occurs
  • onEdit(e) trigger that runs automatically when a user changes the value of any cell in a spreadsheet
fullfine
  • 906
  • 1
  • 1
  • 8
0

This method should work for up to about 3000 rows. More than that and there might be another solution I can link to. Keep the empty rows to a minimum as the longer the sheet is, the slower this will be.

=ARRAYFORMULA(ARRAY_CONSTRAIN(MMULT((ROW(B2:B)>=TRANSPOSE(ROW(B2:B)))*(B2:B=TRANSPOSE(B2:B)),N(C2:C)),COUNTA(B2:B),1))
MattKing
  • 3,748
  • 3
  • 10
-1

SLOW & SHORT:

=INDEX(IF(A2:A="",,MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(B2:B))>=
 ROW(B2:B))*(B2:B=TRANSPOSE(B2:B)), C2:C, 0)), ROW(A2:A)^0)))

enter image description here

FAST & ADVANCED:

=INDEX(MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(
 INDIRECT("B2:B"&MAX(ROW(B2:B)*(B2:B<>"")))))>=ROW(
 INDIRECT("B2:B"&MAX(ROW(B2:B)*(B2:B<>"")))))*(
 INDIRECT("B2:B"&MAX(ROW(B2:B)*(B2:B<>"")))=TRANSPOSE(
 INDIRECT("B2:B"&MAX(ROW(B2:B)*(B2:B<>""))))), 
 INDIRECT("C2:C"&MAX(ROW(B2:B)*(B2:B<>""))), 0)), ROW(
 INDIRECT("B2:B"&MAX(ROW(B2:B)*(B2:B<>""))))^0))

enter image description here

UPDATE: sorted based on date:

=INDEX(IF(A2:A="",,IFNA(VLOOKUP(A2:A&B2:B, {
 INDEX(SORT({A2:A&B2:B, A2:A}, 2, 1),,1), 
 MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(B2:B))>=
 ROW(B2:B))*(INDEX(SORT({B2:B, A2:A}, 2, 1),,1)=
 TRANSPOSE(INDEX(SORT({B2:B, A2:A}, 2, 1),,1))), 
 INDEX(SORT({C2:C, A2:A}, 2, 1),,1), 0)), 
 ROW(B2:B)^0)}, 2, 0))))

enter image description here

player0
  • 69,261
  • 8
  • 33
  • 67