Addition Formula

[JimS]

In my spreadsheet I have a cell where I add every 3rd cell from
another row. Instead of going:

a3+c3+e3+g3+i3+k3...all the way across a long row...

Is there a simple formula to do this?

 

[Pete_UK]

I make that every second cell.

Here's an array* formula that will do it:

=SUM(IF(MOD(COLUMN(3:3),2)=1,3:3))

* Array formula must be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual Enter. If you do this correctly
then Excel will wrap the formula within curly braces { } when viewed
in the formula bar - do not type these yourself. Use CSE again if you
edit the formula.

Hope this helps.

Pete

 

[JimS]

Hi, thanks. I'm a little confused. If my data starts in cell A1, how
would I apply your formula?

 

[Bob Phillips]

If you want the total on the same row as the data, you cannot use the whole
line, but

=SUM(IF(MOD(COLUMN(A3:Z3),2)=1,A3:Z3))

assuming the formula goes in AAR or beyond. If you want to sum more cells,
extend Z3

 

[Dave Peterson]

I would add a new row 1 and use an indicator (an X in each cell) for each column
that should be summed.

=sumif(a1:x1,"x",a4:x4)

(Inserting a new row 1 moved row 3 to row 4.)

 

[ebloch]

I suggest that you keep it simple for faster recalc and to make it easier to
read.

In the row's total cell enter =SUM( and then select the cells you want to
add using a mouse click while holding down <Ctrl>.

Finish with a <)> and <Enter> and copy the result down the column.

 

[Shane Devenshire]

Hi,

Try this

=SUMPRODUCT(A1:L1*MOD(COLUMN(A1:L1),2))

modify the L1 to extend out as far as you want to the right or use

=SUMPRODUCT(1:1*MOD(COLUMN(1:1),2))

to cover all the columns on row 1.

 

[JimS]

Works very nicely, thank you.

I would add a new row 1 and use an indicator (an X in each cell) for each column
that should be summed.

=sumif(a1:x1,"x",a4:x4)

(Inserting a new row 1 moved row 3 to row 4.)

 

[JimS]

Hi, thanks. I see what you're doing here, and it works when I use the
number 2 at the end. But if I change it to 3, 4, 5 etc. it gives some
ridiculously high number. Does it only work for 2?

To test, in new shet across the top in columns A:J, I wrote the
numbers 1:10. I then inserted your formula in A2. It returns 25
(correct) with 2 at the end (is that what mod refers to?) but if I
change it to 3 it returns 52???

Thanks again.

 

[Jason]

The MOD part of the formula simply divides every column number in the
array A1:L1 by 2 and returns the remainder to an array, so if the
divisor is 2 and the range is A1:L1 the column numbers and remainders
returned by the MOD(COLUMN(A1:L1),2) are:

Col Col# Remainder
-----------------------
A 1 1
B 2 0
C 3 1
D 4 0
E 5 1
F 6 0
G 7 1
H 8 0
etc.....

So you can see how the 1s in the above remainder array are every 2nd column.

So, if you replace the 2 with 5 for example, the remainders increase
proportinately (i.e. becauase MOD(4/5), or the remainder of 4 divided by
5 is 4:

Col Col# Remainder
---------------------
A 1 1
B 2 2
C 3 3
D 4 4
E 5 0
F 6 1
G 7 2
H 8 3

The result therefore increases becauase the SUMPRODUCT part of the
formula mutliplies the contents of A1:L1 with the remainder array
(1,2,3,4,0,1,2,3,4 above when using 4 as the divisor argument in MOD).

So as a tool to SUM the Nth column then effectively only 2 works because
only 2 gives an alternating 0,1 sequence in the array.

Use 'Formulas -> Evaluate Formula' to see the above actually working.

Jason