Conditional cumulative sum as array-formula

2
votes

I have one column with values of which I list the cumulative sum in the results-column. But this needs to happen separately for the three (sorted) categories in the second column! Screenshot

See the example sheet here.

I have been trying since hours to solve this rather simple problem without success. Since the formula will be part of an already pretty extensive array-formula it's

  1. Mandatory to be an array-formula

  2. Best if we can get it to work without further =if() questioning

It's very very simple to solve this with a drag-down formula but I can't translate this in to an array-formula for some reason!

=SUMIF($B$2:B2,$B$2:B2,$A$2:A2)

1
google-sheetsarray-formulas
not exactly sure how you want the output, in the ??? cell. do you want only A? only B? All? What should the answer be? - Scott Craner
Output should be the same as column E, right beside! This column is populated by the drag-down formula. My mission: create the same output with an array-formula. ;-) thx for asking! - gazeto

1 Answers

1
votes

In the sheet 'JP' in your shared spreadsheet, I entered in D2

=ArrayFormula((IF(LEN(A2:A), SUMIF(ROW(A2:A),"<="&ROW(A2:A),A2:A),)))

See if that works?

EDIT: for a cumulative sum per category, try this formula

=ArrayFormula(if(len(B2:B100), mmult({mmult(if(B2:B100<>"A", 0,transpose(if(transpose(row(B2:B100))>=row(B2:B100),(B2:B100="A")*(A2:A100), 0))), row(B2:B100)^0), mmult(if(B2:B100<>"B", 0,transpose(if(transpose(row(B2:B100))>=row(B2:B100), (B2:B100="B")*(A2:A100), 0))), row(B2:B100)^0) , mmult(if(B2:B100<>"C", 0,transpose(if(transpose(row(B2:B100))>=row(B2:B100), (B2:B100="C")*(A2:A100), 0))), row(B2:B100)^0)}, {1;1;1}),))

I also added this formula to the sheet you shared.