# Sum above cells matching crtiteria via ARRAYFORMULA

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. 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:

`=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.

• 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... Feb 8, 2022 at 14:44
– 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 || {}
if(Array.isArray(arr)){
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]]
}
``````
• Thanks a lot. It's interesting how such custom functions' performance compares to native ones. Feb 9, 2022 at 22:27