Sum rows every nth column

[2077delta]

Is there a way to sum a range of rows every nth column. For example I want to sum rows 10 - 30 every 4th column. Can I use SUMIFS somehow to do this?

 

[StephenCrump]

Do you mean like this:

Assuming rng: B10:N13
B15: =SUMPRODUCT(rng*(MOD(COLUMN(rng)-COLUMN(INDEX(rng,,1)),4)=0))
Or =SUMPRODUCT(B10:N13*(MOD(COLUMN(B10:N13)-COLUMN(B10),4)=0))

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N
10	1				5				9				13
11	2				6				10				14
12	3				7				11				15
13	4				8				12				16
14
15	136
1

 

[NiMip]

Something like this? You'll need to hit CONTROL+SHIFT+ENTER as the formula uses arrays.

[TABLE="class: grid, width: 414"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]...[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]n=[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sum columns:[/TD]
[TD="align: right"]=SUM(IF(MOD(COLUMN(A$5:AB$104),B1)=0,A$5:AB$104))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0.371155968[/TD]
[TD="align: right"]0.702672[/TD]
[TD="align: right"]0.468813[/TD]
[TD="align: right"]0.410523[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.396036[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0.426536578[/TD]
[TD="align: right"]0.464798[/TD]
[TD="align: right"]0.02317[/TD]
[TD="align: right"]0.242622[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.174385[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]0.424560384[/TD]
[TD="align: right"]0.243813[/TD]
[TD="align: right"]0.21639[/TD]
[TD="align: right"]0.327721[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.001815[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0.043125384[/TD]
[TD="align: right"]0.579772[/TD]
[TD="align: right"]0.04318[/TD]
[TD="align: right"]0.087144[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.319651[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0.711210033[/TD]
[TD="align: right"]0.584506[/TD]
[TD="align: right"]0.583156[/TD]
[TD="align: right"]0.307808[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.160351[/TD]
[/TR]
[TR]
[TD="align: right"]...[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]...[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]0.217490312[/TD]
[TD="align: right"]0.205901[/TD]
[TD="align: right"]0.017295[/TD]
[TD="align: right"]0.554897[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.310382[/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD="align: right"]0.34791251[/TD]
[TD="align: right"]0.840192[/TD]
[TD="align: right"]0.504437[/TD]
[TD="align: right"]0.080458[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.917384[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]0.122595243[/TD]
[TD="align: right"]0.650957[/TD]
[TD="align: right"]0.587608[/TD]
[TD="align: right"]0.886758[/TD]
[TD="align: right"]...[/TD]
[TD="align: right"]0.405192[/TD]
[/TR]
</tbody>[/TABLE]

 

[snjpverma]

Try this
=SUMPRODUCT(N(MOD(ROW(A10:A30),4)=1),A10:A30)