[Solved] Adding columns at regular intervals

[Skyfoogle]

Good afternoon, I have this problem where I need to add the cells:

E1 F1 J1 and K1, and every cell at intervals of 5 to the right of those until CC1

To do it manually I end up with too many cells that Calc doesn't add up everything necessary.

Any help would be welcome, thanks

[RusselB]

Welcome to the forums.
I don't understand why you are having difficulties, as

Code: Select all

=SUM(E1:F1;J1:K1;P1;U1;Z1;AE1;AJ1;AO1;AT1;AY1;BD1;BI1;BN1;BS1;BX1;CC11)

works fine on my machine given the parameters in your post.
If this does not work for you, please upload a sample spreadsheet with dummy data showing the problem, the actual result and the expected result.

[Skyfoogle]

So, the formula I need is:

=If(Sum(orange boxes)-sum(green boxes)<0;0;Sum(Orange boxes)-sum(green boxes))

if there are more than like 16 green and orange boxes, then the formula cuts out - ideally as I'm making a spreadsheet for cumulative totals entered in this way (as there will be about 100 entries each with unique totals and data) it's not got an ending, I can just keep dragging the formula.

[RusselB]

That's more complicated.
The easiest way to handle this, is to use a helper row to calculate the sum of all of the rows above it, then a helper column (or two) to do the comparisons between Green and Orange.
See row 41 in this attachment to see my methodology.
Using a helper row might be your only option to keep the level of calculations feasible.

[Skyfoogle]

Thanks very much, I will use this solution instead. Just thought there may be an easier way, like there is in excel. Thanks for your help.

[RusselB]

There are other ways of doing this, but one of your biggest problems is with the layout of your data.. having several sets of 4 columns with a blank in between, and each set being divided into 2.
I have no idea as to why you have it set up this way in order to, possibly, suggest a better layout for your computations.
What I suggested just seemed the easiest to implement and, if there is a problem, it'd be easier to find where the problem is by keeping it in simple steps.
If you wanted, you could simply change the SUM for the Green boxes from =sum to -sum
Then you could just sum up the entire row in your IF without having to do any more mathematics.
If you don't understand what I'm saying, I'll redo my version using the above method... it might even be a bit more efficient, though the difference will be very small.

[RusselB]

Thanks very much, I will use this solution instead. Just thought there may be an easier way, like there is in excel. Thanks for your help.

Do you know how this would be done in Excel? If you do, providing that method may enable us to determine what (if any) comparable method is available.

[Skyfoogle]

They use either a user defined formula, or something like

=SUMPRODUCT((MOD(COLUMN(6:6),5)=1)*(6:6))

The mod function will return a value of 1 every 5, and so those are the only 'true' cells, these are multiplied by whichevdr cell they correspond to and added in sumproduct.
As x*1=x, the numbers won't get distorted. The column function is just to define which columns.