I have a sheet where data is added from time to time. The data is rather simple - object name and its quantity. I want to have a column with sum of object's quantity up to mentioned row. This is easily achieved via regular formulas (SUMIF($A$2:$A2;$A2;$B$2:$B2)) but I can't manage a solution via ARRAYFORMULA as SUMIFS does not work with it and I had no success with giving dynamic range to SUMIF (arrayformula(SUMIF(B2:B&ROW(C2:C);B2:B;C2:C))). Here's the spreadsheet - https://docs.google.com/spreadsheets/d/1Omgc62Q3OHxwpNBJxMiiJGFToRfzdLs6l2EkrJ_koew/edit?usp=sharing This seems like a rather easy task but I was not able to implement an easy solution without dozens of nested functions. I was not able to find a solution on Stackexchange as well.


3 Answers 3


I added a new sheet ("Erik Help") to your spreadsheet with a shorter version of the formula offered by "ztiaa":

=ArrayFormula({"Header Here";IF(A2:A="";;MMULT((A2:A=TRANSPOSE(A2:A))*(ROW(A2:A)>=TRANSPOSE(ROW(A2:A)))*(TRANSPOSE(B2:B));ROW(A2:A)^0))})

This also includes the header, which you can change within the formula itself. I recommend including the header in array formulas where possible, because it serves as an extra safeguard against accidentally deleting the formula if you were to delete the first row of data.


I entered the following in your sheet:


We are dynamically creating the ranges with INDIRECT otherwise it will get very slow.

  • 1
    Thanks a lot. Hard to believe you have to write such a monstrous function to achieve such a simple result. I hope they will make more functions compatible with arrayformula...
    – pacmanito
    Feb 8, 2022 at 14:44
  • glad I could help! Please see What should I do when someone answers my question?
    – user290033
    Feb 9, 2022 at 0:23

You can also use a simple custom function. I added it to your sheet and the result in column F:

function myFunction(arr, dict) {
  dict = dict || {}
    return arr.map(a=>myFunction(a, dict))

  const [obj, qty] = arr
    return ['']
  dict[obj] = dict[obj] || 0
  dict[obj] += qty
  return [dict[obj]]  
  • Thanks a lot. It's interesting how such custom functions' performance compares to native ones.
    – pacmanito
    Feb 9, 2022 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.