Taking the Cumulative sum of a Column while Selecting only a Specific Sum within that column

Question

I have a table that has a symbol column and a corresponding number for it. Let's say I want the cumulative sum of only specific symbols such as ABC and DEF in this case , How can I build a formula that only takes the cumulative sum of only ABC and DEF and other symbols that I would like to isolate for if necessary rather than the cumulative sum of all the symbols for a given date. An example of a table is below where the answer of the cumulative sum of only ABC and DEF is highlighted in the yellow cell for a specific date. I would also like the formula to be able to choose any specified date.

Answer 1

You can use FILTER to get a list of qualifying vaues from your table, and SUM that.

=SUM(FILTER(NameOfTable[stat 1], <Criteria>))

The trick is to specify the filter criteria. You ask for a Date Range, and a list of qualifying Symbols.

Dates are easy

(NameOfTable[Date]>=G2)*(NameOfTable[Date]<=G3)

Qualifying a list of symbols is a bit harder. Using a new function BYROW it can be done as (available only to insiders at time of writing)

(BYROW(--(NameOfTable[Sym]=TRANSPOSE(FILTER(I:I,I:I<>""))),LAMBDA(array,SUM(array))))

The whole function:

=SUM(FILTER(NameOfTable[stat 1],
  (NameOfTable[Date]>=G2)*
  (NameOfTable[Date]<=G3)*
  (BYROW(--(NameOfTable[Sym]=TRANSPOSE(FILTER(I:I,I:I<>""))), LAMBDA(array, SUM(array))))
))

An alternative, not using BYROWS

MMULT(--(NameOfTable[Sym]=TRANSPOSE(FILTER(I:I,I:I<>""))),1-N(FILTER(I:I,I:I<>"")))

So the whole formula is

=SUM(FILTER(NameOfTable[stat 1],
  (NameOfTable[Date]>=G2)*
  (NameOfTable[Date]<=G3)*
  MMULT(--(NameOfTable[Sym]=TRANSPOSE(FILTER(I:I,I:I<>""))),1-N(FILTER(I:I,I:I<>"")))
))

_{Change NameOftable to your actual table name}