Google-sheets – ArrayFormula to calculate stock position by date

google sheetsgoogle-sheets-arrayformula

I am creating a stock transaction tracking sheet where I enter buys and sells etc. I have a formula that can calculate a running position total but I cannot figure out how to convert it to an array formula.

enter image description here

The formula I have so far is

SUM(INDEX(FILTER(A:D, B:B=B2, row(B:B) <= row(B2)),,4))

Here's a copy of the sheet

Best Answer

You want a running sum of values in Column D based on a conditional value in each row of Column B.

Your scenario is VERY similar to this Webapps precedent ArrayFormula to compute running average for groups of rows. In that case, the desired result was a running average. I have modified the accepted answer by @AdamL. I have also added an alternative found on infoinspired

Assuming all entries in Column B are in sequence (grouped)

Use this formula:

=ArrayFormula(IFERROR((SUMIF(ROW(B2:B),"<="&ROW(B2:B),D2:D)-HLOOKUP(0,SUMIF(ROW(B2:B),"<"&ROW(B2:B),D2:D),MATCH(B2:B,B2:B,0),0))))

Assuming all entries in Column B are NOT in sequence (grouped)

OPTION#1

=ArrayFormula(IF(LEN(B2:B),MMULT((ROW(B2:B)>=TRANSPOSE(ROW(B2:B)))*(B2:B=TRANSPOSE(B2:B)),--D2:D),))

OPTION#2

Solution from infoinspired Array Formula for Conditional Running Total in Google Sheets

=ArrayFormula(MMULT(N(ROW(B2:B7)>=TRANSPOSE(ROW(B2:B7)))*N(B2:B7=TRANSPOSE(B2:B7)),N(D2:D7)))

Note: the MMULT function (Doc ref) is often avoided because it can slow down recalculation. It is generally accepted that it is slow with an open ended range (such as B2:B) but faster over a smaller number of rows where the range can be capped (such as B2:B8). AdamL's answer should also be read for his discussion of the pros and cons of the "MMULT" vs "non-MMULT" alternatives, including their limitations and effect of processing speed.

Example

Example

Related Topic