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.
Google Sheets – Sum Cells Matching Criteria via ARRAYFORMULA
google sheetsgoogle-sheets-arrayformula
Related Topic
- Google-sheets – Arrayformula using cells as parameters
- Google Sheets – ArrayFormula for Consequential Sum-Up
- Google Sheets – ArrayFormula for Conditional Consequential Sum-Up
- Google-sheets – How to reference a dynamic range in Google Sheets Script
- Google-sheets – Google Sheets data formatting and extractions into more workable, easier to read format
- Google-sheets – How to find and get data from only a certain, dynamic set of columns in another Sheet
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.