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

 

[daniels012]

I tried Chips in my fist example

Michael

 

[daniels012]

Actually it has to do with changing the number to 6 instead of 5??

Michael

 

[daniels012]

This is the formula I am trying from Chip's Suggestion:

=SUM(IF(MOD(ROW($J$11:$J$150)-ROW($J$11)+1,7)=0,$J$11:J$150,0))
Yes I am using Crtl+Shift+Enter

it will not give the proper results?
I am not sure why?

Michael

 

[daniels012]

Beate,
Why when I change your example to 6 instead of 5, it does not work?

Michael

 

[Beate Schmitz]

Hi Daniel,

formula depends on actual row-No., example starting in row 6:

<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:23px;" /><col style="width:80px;" /><col style="width:23px;" /><col style="width:183px;" /></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; " >6</td><td style="background-color:#ffcc00; font-family:Verdana; text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="font-family:Verdana; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="background-color:#ffcc00; font-family:Verdana; text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">34</td><td style="font-family:Verdana; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:2px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ccffcc; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">2</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="background-color:#ccffcc; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">38</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">3</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">42</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#99ccff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">4</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="background-color:#99ccff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">46</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#cc99ff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">5</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="background-color:#cc99ff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">50</td><td style="background-color:#cc99ff; font-family:Verdana; text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; ">attention: Formula differs!</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffcc00; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">6</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ccffcc; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">7</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">8</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#99ccff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">9</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#cc99ff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">10</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffcc00; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">11</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ccffcc; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">12</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">13</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#99ccff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">14</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#cc99ff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">15</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="background-color:#ffcc00; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">16</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="background-color:#ccffcc; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">17</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">18</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="background-color:#99ccff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">19</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:1px; border-right-color:#969696; border-bottom-color:#000000; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="background-color:#cc99ff; font-family:Verdana; text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:2px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#969696; border-left-color:#000000; ">20</td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#969696; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:2px; border-right-color:#000000; border-bottom-color:#969696; "> </td><td style="font-family:Verdana; border-right-style:solid; border-bottom-style:solid; border-right-width:2px; border-bottom-width:2px; border-right-color:#969696; border-bottom-color:#969696; "> </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 >C6</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$6:A$25)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>-5)</span>*A$6:A$25)</td></tr><tr><td >C7</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$6:A$25)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>-5)</span>*A$6:A$25)</td></tr><tr><td >C8</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$6:A$25)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>-5)</span>*A$6:A$25)</td></tr><tr><td >C9</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$6:A$25)</span>,5)</span>=ROW<span style=' color:#0000ff; '>()</span>-5)</span>*A$6:A$25)</td></tr><tr><td >C10</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(A$6:A$25)</span>,5)</span>=0)</span>*A$6:A$25)</td></tr></table></td></tr></table>

Regards,
Beate

 

[daniels012]

So in your sample -5 represents row 6??

What if I put the formula in Cell J2 and my ranges are in J11:J150? I then need starting at J11 every 7 cell down to sum;
J11........ J18....... J25......... etc.

What would my formula be in J2? \

Michael

 

[Beate Schmitz]

Hello Daniel,

<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;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >J</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</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:#ffff00; text-align:right; ">1350</td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td></tr><tr style="height:4px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</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:#ff0000; text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">7</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ff0000; text-align:right; ">8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">9</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">10</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">11</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">12</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">13</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">14</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="background-color:#ff0000; text-align:right; ">15</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">16</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">17</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 >J2</td><td >=SUMPRODUCT(<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(ROW<span style=' color:#ff0000; '>(J$11:J$150)</span>,7)</span>=4)</span>*J$11:J$150)</td></tr></table></td></tr></table>

The sheet is filled this way up to row 150, as asked by you.

Because you now start in row 11 and you want to add every 7th cell: 11-7=4 . 4 is the balance remaining after division by 7 in each required cell.

 

[Aladin Akyurek]

Re: Easy way to add every 6th column

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

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+0,6)=0),A1:A100)

will sum every 6th value in A1:A100, starting with the start cell's value (here A1).

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1)+1,6)=0),A1:A100)

will sum every 6th value in A1:A100, starting with the 6th cell's value (here A6).

If you want to sum A2:An at every 6th, starting with the start cell's value, that is, with A2...

=SUMPRODUCT(--(MOD(ROW(A2:A100)-ROW(A2)+0,6)=0),A2:A100)

Maybe you are looking for...

B1, copied down:

=SUMPRODUCT(
     --(MOD(ROW(INDEX($A$1:A$100,ROWS($B$1:B1)):$A$100)-ROW(INDEX($A$1:A$100,ROWS($B$1:B1)))+0,6)=0),
   INDEX($A$1:A$100,ROWS($B$1:B1)):$A$100)

 

[daniels012]

Beate,
That seems to work with all but this one:
=SUMPRODUCT((MOD(ROW(J$14:J$150),7)=7)*J$14:J$150)
I am not sure if it is somehow because of the 7's.
But by using your example, 14-7=7 this should work.

Thank you for all your help so far.

Michael

 

[Beate Schmitz]

Hello Daniel,

that cannot work. Because 14:7=2 but no balance left. You will never have a balance of 7 when you devide by 7.

Clear now?