CODEWITHSUNDEEP
libreoffice-calc
spam
spam

Reputation: 1993

Sum special cells

I am trying to sum all cells: B5, B8, B11, B14 ... (but not B2) D5, D8, D11, D14 ... (but not D2) F5, F8, F11, F14 ... (but not F2) ...

so in other words: every cell, where column is even (A=1, B=2, ...) and row modulo 3 equals 2, except 2.

So I have created function:

=SUM(IF(AND(MOD(ROW(B5:Z200), 3) = 2, ISEVEN(COLUMN(B5:Z200))), B5:Z200 ,0))

as well as

=SUM(IF(AND(MOD(ROW(B5:Z200), 3) = 2, MOD(COLUMN(B5:Z200), 2) = 0), B5:Z200 ,0))

but both return me unexpected result - it works like it ignores column constraint (sums where row modulo 3 equals 2 except 2, but from every column).

When I'm pasting them as array formula (I do not understand what that is) then in both cases result is 0.

Upvotes: 1

Views: 181

Answers (1)

Jim K
Jim K

Reputation: 13790

It is possible to sum every nth row in a single column: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Conditional_Counting_and_Summation#Tips_and_Tricks:_Summing_Every_nth_Row

However this will not work with a two-dimensional cell reference such as B5:Z200, because ROW only returns a one-column array. Likewise COLUMN only returns a one-row array.

The solution is to first sum the columns using a formula for each column, then sum these results. So put the following formula in cell B1 (or whichever row is available).

=SUMPRODUCT(B5:B200, MOD(ROW(B5:B200), 3) = 2)

Now drag to fill this formula over to cell Z1. Then sum all of them with this formula.

=SUMPRODUCT(B1:Z1, MOD(COLUMN(B1:Z1), 2) = 0)

Note: SUMPRODUCT does not need to be entered as an array formula.

EDIT:

If it is required to put this into a single massive formula, then it can be done. However it will be more difficult to write and maintain. Simply sum the formulas for each column. In this case, there is no need for taking the mod of the column; just include the columns you need.

Below is a formula that covers columns B through F. Simply keep following this pattern until the formula includes column Z.

=SUM(SUMPRODUCT(B5:B200, MOD(ROW(B5:B200), 3) = 2), SUMPRODUCT(D5:D200, MOD(ROW(D5:D200), 3) = 2), SUMPRODUCT(F5:F200, MOD(ROW(F5:F200), 3) = 2))

Upvotes: 1

Related Questions