SUM will exclude cells [the selected range belonging to the range of included SUM]

[ironhak2]

STEPS TO REPRODUCE

1. Create a column of values, like insert random numbers from A1 to A5.
2. Select these numbers.
3. Click the arrow next to the formula button.
4. Select SUM.
5. On A6 you’ll have the sum of the previously selected range.
6. Then select the new range, from A1 to A6, press the arrow next to sigma and sum again.
7. Then as you see, the range for the sum gets shrinked to inlude only the alst cell!
   

I’m trying to fix for a co-worker who’s workflow heavily depends on doing this kind of passage. He told me that on excel everything works good. Any idea on how to fix this? Thanks.

What I expect

When I select a range and do the sum, of course I want the sum of that range.

[mikekaganski]

@ironhak2

He told me that on excel everything works good.

I don’t know what they call “works good”, but it works different, and definitely not as your co-worker seems to expect. Tested both with Excel 2016 and current Excel 365.

The second “Sum” over the combined A1:A6 range will simply do nothing in Excel; while it adds a new sum in A7 for A6 in Calc. Both imply, that you do not intend to sum a range with its own sum.

Well, your description lacks any “I expect …” part; so may be that’s what you actually expect?

[ironhak2]

My colleague uses excel on a mac and it always works.

This happens also on other scenarios, for example when the first value in a range is a formula, then the sum would be executed but the first value excluded.

[mikekaganski]

So what should be the “correct” behavior: the one that your colleague claims on macOS, or that is actually happening in Excel on Windows and on the Web?

I would still ask to provide a screen cast of the macOS behavior.

[ironhak2]

I can’t because we no longer have access to the machine, hence the reason for using libreoffice.

[mikekaganski]

Well: since there is no “standard”, I’d say, your colleague would better learn different workflows, to not be locked to own habits that disallow productive work in anything except a single system.