Cumulative sum function on variably ordered arrays

Occasional Visitor

Is there a cumulative sum function solution to variably ordered array used in an if statement? E.g. if($a$1<$a$7,sum($a$1:an->a7),sum($a$7:an->a1))

i.e. point of summation is different when the order is ascending versus descending.

If I use this syntax the relative coordinates in the descending order alternative get jumbled when I drag the formulae entered  in the range from b1:b7. Alternatively, the ascending order function gets jumbled if I drag/copy from b7:b1


Do I really have to hard code the inverse cumulative sum range because it does not follow the direction of the dragged/copied formula? 🤮

1 Reply



Given the commutative property of addition, what difference would it make?


So you must be trying to do something to which that property doesn't apply. (Or I haven't understood your description; also entirely possible.)


Could I suggest ,therefore, that you flesh out the example with a set of representative numbers, and a description of the task or the goal in English words, not in the language of Excel. Leave the translation into Excel to those of us who might offer a solution.