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.

Thank you for your answer, do you know a similar formula using Office 2019? — TeelowxD, Aug 14 '21 at 01:31

chris neilsen · Accepted Answer · 2021-08-13T00:21:56.270

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}

Thank you for your answer, do you know a similar formula using Office 2019? — TeelowxD, Aug 14 '21 at 01:31

score 0 · Answer 2 · answered Aug 13 '21 at 00:34

You can string together statements with "*" (for "and") and "+" (for "or") with the FILTER function to filter on multiple criteria.

If you want a certain date and certain symbols, use something like

=SUM(FILTER(sum_range, 
((date_range=date)*
((symbol_range=symbol1)+(symbol_range=symbol2)+...))))

For example:

=SUM(FILTER($C$2:$C$10,
(($B$2:$B$10=E3)*
(($A$2:$A$10=E4)+($A$2:$A$10=E5)))))

score 0 · Answer 3 · answered Aug 16 '21 at 11:29

If you want the sum of only specific Single Date, Then use it

=SUMPRODUCT((--(A2:A10=E1)+--(A2:A10=F1))*IF(G1="",1,(B2:B10=G1)),C2:C10)

If you want the Sum till any Date Then Use it

=SUMPRODUCT((--(A2:A10=E1)+--(A2:A10=F1))*IF(G1="",1,(B2:B10<=G1)),C2:C10)

If you want Sum between Two given Dates Then

=SUMPRODUCT((--(A2:A10=E1)+--(A2:A10=F1))*IF(G1="",1,(B2:B10<=G1))*IF(H1="",1,(B2:B10>=H1)),C2:C10)

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

3 Answers3