Google Sheets – Sum Cells Matching Criteria via ARRAYFORMULA

google sheetsgoogle-sheets-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.

table

Best Answer

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.

Related Topic