Sum Every Nth Row

[AlbertS]

I have googled this and found the technical answer. My problem is modifying the canned text answe r(it seems to be exactly the same on each reply I've found).

I have D10:D249. I need to sum D10, D13, D16, D19 (every 3rd row) in cell D250.

HELP is always appreciated. If you can break down the answer in baby talk, so I can understand why it works, all the better!

Thanks in advance.

 

[shaikhrulez]

Thanks for all the tips & tricks,

I encountered an error in a formula you've posted. when I'm trying to sum the below mentioned range for summing up every 3rd row with this formula =SUMPRODUCT($A$1:$A$6*(MOD(ROW($A$1:$A$6),3)=1)) it is giving result 5 whereas it should hv been 3 + 6 = 9. But after making slight variation in your formula i.e. =SUMPRODUCT($A$1:$A$6*(MOD(ROW($A$1:$A$6),3)=0)) then it works absolutely fine. Why it is so?

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

Furthermore, I'd like to ask, can we modify the above formula as per our requirement? What If I'm looking to sum value in every 2nd row? Can it be done by changing 3 to 2 in your formula?

Thanks

 

[Aladin Akyurek]

Thanks for all the tips & tricks,

I encountered an error in a formula you've posted. when I'm trying to sum the below mentioned range for summing up every 3rd row with this formula =SUMPRODUCT($A$1:$A$6*(MOD(ROW($A$1:$A$6),3)=1)) it is giving result 5 whereas it should hv been 3 + 6 = 9. But after making slight variation in your formula i.e. =SUMPRODUCT($A$1:$A$6*(MOD(ROW($A$1:$A$6),3)=0)) then it works absolutely fine. Why it is so?

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

Furthermore, I'd like to ask, can we modify the above formula as per our requirement? What If I'm looking to sum value in every 2nd row? Can it be done by changing 3 to 2 in your formula?

Thanks

You seem not to have read the whole thread. The formula for summing values at every Nth row is:

=SUMPRODUCT($A$1:$A$6,--(MOD(ROW($A$1:$A$6)-ROW($A$1),n)=0))

where n = 1,2,...,N [ N = ROWS(Reference) ].

If you want to sum values at every 2nd row, set n to 2.