Over riding Sum formula

3.83K viewsTips and Techniques

How do you override the sum formula for a beginning or ending period balance?

I have a cash flow statement for example with 12 months. When I use the sum formula, it adds ALL the rows including the opening and closing cash balances.

What I really want is the opening cash balance in the sum column to be the cash balance at the beginning of the period, not the sum of all the monthly beginning cash balances which is what the sum formula currently does. If I use the Skip command, the opening balance cell in the sum column is blank so it does not accomplish what I need.

Is there a way to add further commands to the sum formula to pick up the correct beginning cash balance without having to write an additional separate formula?

Any help is greatly appreciated. Thanks very much.


Hi – You may be able to use an IF in conjunction with the ‘@’ operator to detect if the sum formula is in an opening or closing balance item. If so you can do something different, otherwise it will sum.

Attached is a prototypical example that may help you with the concept. Hope it helps.


Latest Questions

Qloud Losing Formatting 4 Answers | 0 Votes
Meditation on timelines 3 Answers | 0 Votes