Row Adding every 20th cell question

[agoodson56]

I there a way to add up every 20th cell in a row for a total of 40 cells?

Trying to do the =U11+AO11+BI11+CC11 and so on will take forever as I have 60 rows to do this for.

 

[Ragnar1211]

Off the top of my head I cannot think of how to do this without a helper row that marked the cells you want to add.

If you have a helper row that marked each cell you want totaled you could then use a sumif formula to get the total

For example (assuming row 10 has the helper text of "x" in the cells that need to be added):

=sumif($U$10:$CC10,"x",$U11:$CC11)

 

[agoodson56]

What is a helper row and how would I add it?

 

[Ragnar1211]

A helper row is a row that assists the formula identify the cells it needs to consider.

Typically a helper row is hidden from display or printing when it is not required to be seen for adjusting its values.

The row can be anywhere in the sheet, but I would typically add it directly above the row you are working with, as suggested in my example above.

<title>Excel Jeanie HTML</title>[B]Sheet1[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:150px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD]Helper Row:[/TD]
[TD]x[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]x[/TD]
[TD]*[/TD]
[TD]x[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD]Value Row[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD][B]Spreadsheet Formulas[/B][/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]H5[/TD]
[TD]=SUMIF($B$4:$G$4,"x",$B$5:$G$5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.excel-jeanie-html.de/index.php?f=1"] Excel Jeanie HTML 4 [/URL]

 

[agoodson56]

Thank you! That is so COOL!

 

[Rick Rothstein]

I believe this formula will work without the need for the helper cells...

=SUMPRODUCT(--(MOD(COLUMN(U11:ADU11)-1,20)=0),U11:ADU11)