Sum above cells matching crtiteria via ARRAYFORMULA

Question

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.

Answer 1

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.

Answer 2

I entered the following in your sheet:

=ArrayFormula(IFERROR(1/(MMult((Row(Indirect("A2:A"&Max(Row(A:A)*(A:A<>""))))>=Transpose(Row(Indirect("A2:A"&Max(Row(A:A)*(A:A<>""))))))*(Indirect("A2:A"&Max(Row(A:A)*(A:A<>"")))=Transpose(Indirect("A2:A"&Max(Row(A:A)*(A:A<>"")))));Indirect("B2:B"&Max(Row(A:A)*(A:A<>"")))))^-1))

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

Answer 3

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 || {}
  if(Array.isArray(arr[0])){
    return arr.map(a=>myFunction(a, dict))
  }

  const [obj, qty] = arr
  if(!obj){
    return ['']
  }
  dict[obj] = dict[obj] || 0
  dict[obj] += qty
  return [dict[obj]]  
}