There is always some uncertainty involved in expressions like "sum/average ever other cell or every 3rd or 4th cell." The question arises as to whether to start summing/averaging from the first cell of the target range or at Nth other cell.
I believe a well-parameterized UDF is the way to go.
A flexible UDF should accept a Range that can be either vertical (a range in a column) or horizontal (a range in a row), a Start cell (either from the first cell on or from Nth cell on), a Function parameter (at least for SUM and AVERAGE), and N which indicates (every Nth) cell to be included in Function's domain.
The sheet figure shows a bit more flexible formula approach for vertical ranges.
<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>Microsoft Excel - aaSum&AvgEvery.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Options(<U>O</U>) Tools(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>I12</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=2 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Start At (either 1 or D2)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>Sum Every</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range1 (in turquoise)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range1))')><FONT FACE=MS P???? COLOR=#000000>44</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range2 (in gold)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range2))')><FONT FACE=MS P???? COLOR=#000000>44</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Start At (either 1 or D2)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>14</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>Average Every</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range1 (in turquoise)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)*(Range1))/MAX(1,SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)+0))')><FONT FACE=MS P???? COLOR=#000000>11</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>18</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range2 (in gold)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$6=$D$7),$D$7)=0)*(Range2))/MAX(1,SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$6=$D$7),$D$7)=0)+0))')><FONT FACE=MS P???? COLOR=#000000>11</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>14</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>13</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>18</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>14</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Sum&AvgEvery</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>
<FONT COLOR=#339966 SIZE=1>The above image was automatically written by HtmlMakerVer1.12</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT>
</CENTER>
D1 must be either 1 or equal to D2.
D2 records N of the every Nth cell.
D3 has a formula for summing:
=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range1))
D4 likewise for the second vertical range.
D8 has a formula for averaging:
=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)*(Range1))/MAX(1,SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)+0))
D9 likewise for the second vertical range.
Aladin