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

daniels012

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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? :cry:\

Michael
 
Upvote 0
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.
 
Upvote 0
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
Click to expand...

=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:

Code: 
=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)
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Unable to copy formula down sheet if data isn't there.
Replies
3
Views
195
Fluff
Fluff
GomaPile
  • Question Question
ShowDataForm
Replies
1
Views
311
DanteAmor
DanteAmor
P
  • Question Question
Count names based on enrollment
Replies
2
Views
271
paulkc
P
R
  • Solved
IF/MATCH statement that includes the SUM
Replies
5
Views
368
Fluff
Fluff
J
  • Solved
Formula for adding cells (with a row interval)
2
Replies
14
Views
892
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top