I have created a formula that can track deltas based on a type that is tolerant to sorting and filtering.
Currently I have to use the formula in each row of the table I have created and have tried to create an arrayformula version of the formula but an not having much success
For the sake of clarity I created named ranges for the values in each column
I have a formula with the behavior I want. i.e. In column F entries before the date in same row of column C are added up such that type A entries are added and Type B & C are subtracted. The formula is consistent regardless of sorting and filtering.
When I try to make it in the form of an arrayformula I get errors:
**=Arrayformula(SUMPRODUCT(Amount,Date<=(C4:C),((Type<>"A")-1)+((Type="A")1)))
error because of size mismatch
>Solution :
There is no way to use arrayformula directly in your original formula because it would be ambiguous. (Though there might be alternatives altogether.)
Date<=C4:C9 should simply mean cell-wise comparison but you want an array mapping on the 2nd operand with the comparison expanded to the whole range of Date for each cell in the 2nd operand. That is fundamentally not what arrayformula accommodates.
- Other critiques: It’s better to avoid spurious brackets for readability. In separate scenarios where you do want cell-wise comparison, remember to match range size.
You can instead use map as follows.
=map(C4:C,lambda(c,if(isblank(c),,SUMPRODUCT(Amount,Date<=c,((Type<>"A")*-1)+((Type="A")*1))))