Easy way to sum every 6th column [[SOLVED]]

[daniels012]

Is there an easy way to add every sixth column?
Example:
=A1+A6+A11+A16
Then
A2+A7+A12+A17

I am all the time doing something like this. Is there a formula or something of that nature that would sum every 6th cell down starting a a given point?

Thank you!
Michael

 

[arthurbr]

Hi Michael

Chuck has your answer :http://cpearson.com/excel/excelF.htm#SumEveryNth

 

[Beate Schmitz]

Hello Michaels,

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:32px;" /><col style="width:80px;" /><col style="width:177px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffcc00; text-align:right; ">1</td><td > </td><td style="background-color:#ffcc00; text-align:right; ">34</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ccffcc; text-align:right; ">2</td><td > </td><td style="background-color:#ccffcc; text-align:right; ">38</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ccffff; text-align:right; ">3</td><td > </td><td style="background-color:#ccffff; text-align:right; ">42</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#99ccff; text-align:right; ">4</td><td > </td><td style="background-color:#99ccff; text-align:right; ">46</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#cc99ff; text-align:right; ">5</td><td > </td><td style="background-color:#cc99ff; text-align:right; ">50</td><td style="background-color:#cc99ff; text-align:center; ">attention: Formula differs!</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffcc00; text-align:right; ">6</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ccffcc; text-align:right; ">7</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ccffff; text-align:right; ">8</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#99ccff; text-align:right; ">9</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#cc99ff; text-align:right; ">10</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffcc00; text-align:right; ">11</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ccffcc; text-align:right; ">12</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ccffff; text-align:right; ">13</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#99ccff; text-align:right; ">14</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#cc99ff; text-align:right; ">15</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffcc00; text-align:right; ">16</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ccffcc; text-align:right; ">17</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ccffff; text-align:right; ">18</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#99ccff; text-align:right; ">19</td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#cc99ff; text-align:right; ">20</td><td > </td><td > </td><td > </td></tr></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$1:A$20)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>)</span>*A$1:A$20)</td></tr><tr><td >C2</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$1:A$20)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>)</span>*A$1:A$20)</td></tr><tr><td >C3</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$1:A$20)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>)</span>*A$1:A$20)</td></tr><tr><td >C4</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$1:A$20)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>)</span>*A$1:A$20)</td></tr><tr><td >C5</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$1:A$20)</span>,5)</span>=0)</span>*A$1:A$20)</td></tr></table></td></tr></table>

 

[daniels012]

I tried this:
=SUM(IF(MOD(ROW($A$6:$A$40)-ROW($A$6)+1,A1)=0,$A$6:A$40,0))
And it gives 0 as as the result. The result should be 12?

The values in the cells are:
A1 = 5
A6 = 4
A11 = 2
A16 = 6

Ideas of what may be wrong?


Michael

 

[daniels012]

Beate,
This works beautifully!

thank you so much!!

 

[daniels012]

Actually it did not work.
When I start at a different row other that the 1st row, it will not work.

michael

 

[xld]

Hi Michael

Chuck has your answer :http://cpearson.com/excel/excelF.htm#SumEveryNth

He calls himself Chip.

 

[daniels012]

No actually it just doesn't add the proper cells?

Michael

 

[xld]

Is this what you mean

=SUMPRODUCT(--(MOD(ROW(A$1:A$20),5)=ROW(A1)-1),A$1:A$20)

 

[arthurbr]

I tried this:
=SUM(IF(MOD(ROW($A$6:$A$40)-ROW($A$6)+1,A1)=0,$A$6:A$40,0))
And it gives 0 as as the result. The result should be 12?

The values in the cells are:
A1 = 5
A6 = 4
A11 = 2
A16 = 6

Ideas of what may be wrong?


Michael

I didn't check the formula but did you enter it as an array formula?
(with ctrl+shift+enter)