Sum every 'nth' row - NOT WORKING

[CAT2016]

Hi

I have been reading a few threads here today about adding cells together every 'nth' row and I am having some success and some failures. I have managed to add up a range of cells (every 6) in a column using this formula

=SUMPRODUCT($C$10:$C$154*(MOD(ROW($C$10:$C$154),6)=1))

but it doesn't add the last cell even though it is included in the range.

I am also adding up another range of cells, again every 6 in the same column but on a different line using this formula

=SUMPRODUCT($C$11:$C$154*(MOD(ROW($C$11:$C$154),6)=1))

but it gives me the same total as the cell above it, also a total cell of the first formula, even though all the cells indicated in the range are of a $0 amount.

I have tried the formula in various different ways and MrExcel's was the closest and most accurate but I just can't get the correct totals using this formula in each of the 'total' cells.

What am I doing wrong????

QUOTED TOTALS $4.00 $3.00 $0.00
ACTUAL TOTALS $4.00 $3.00 $0.00

 

[RoryA]

Your formula adds rows whose row number modulo 6 is equal to 1, which would be rows 1, 7, 13 and so on. So for both of your ranges you get rows 13, 19, 25 and so on. If you want every 6 rows of the range you specify, you need to offset the row number appropriately.

 

[Michael M]

That's because you don't have any even set of sixes....you have 24 sets of 6 and 1 set of 1 number....which I'm guessing is the last one and the one you are not getting included.

 

[MarcelBeug]

One way of offsetting the row number would be:

=SUMPRODUCT($C$10:$C$154*(MOD(ROW($C$10:$C$154[B])-ROW($C$10)+1[/B],6)=1))

which will give you the sum of every 1st, 7th etcetera element of the range.

 

[azumi]

Maybe

=SUMPRODUCT(--(MOD(ROW(A1:A24),6)=0),A1:A24)

 

[Aladin Akyurek]

1.

=SUMPRODUCT($C$10:$C$154,--(MOD(ROW($C$10:$C$154)-ROW($C$10),6)=0))

will sum every 6th cell from C10:C154, starting with C10.

2.

=SUMPRODUCT($C$10:$C$154,--(MOD(ROW($C$10:$C$154)-ROW($C$10)+1,6)=0))

This starts summing from the 6th cell of the range on.

 

[CAT2016]

Hi

thanks to everyone who replied, I am about to try your suggestions so fingers crossed there is a solution in there somewhere. Thanks again and I may be back very soon!!!

Hi

I have been reading a few threads here today about adding cells together every 'nth' row and I am having some success and some failures. I have managed to add up a range of cells (every 6) in a column using this formula

=SUMPRODUCT($C$10:$C$154*(MOD(ROW($C$10:$C$154),6)=1))

but it doesn't add the last cell even though it is included in the range.

I am also adding up another range of cells, again every 6 in the same column but on a different line using this formula

=SUMPRODUCT($C$11:$C$154*(MOD(ROW($C$11:$C$154),6)=1))

but it gives me the same total as the cell above it, also a total cell of the first formula, even though all the cells indicated in the range are of a $0 amount.

I have tried the formula in various different ways and MrExcel's was the closest and most accurate but I just can't get the correct totals using this formula in each of the 'total' cells.

What am I doing wrong????

QUOTED TOTALS $4.00 $3.00 $0.00
ACTUAL TOTALS $4.00 $3.00 $0.00

 

[CAT2016]

MarcelBeug



Re: Sum every 'nth' row - NOT WORKING
One way of offsetting the row number would be:


Code:
=SUMPRODUCT($C$10:$C$154*(MOD(ROW($C$10:$C$154)-ROW($C$10)+1,6)=1))which will give you the sum of every 1st, 7th etcetera element of the range.




Hey MarcelBeug you are an absolute champion!!! Your formula was the one that worked best without any errors so a huge THANK YOU!!!!!! I don't really understand what you wrote (complete novice here) but it worked so I am happy with that!

Thanks again for all those that replied to my question you are all pretty good too

 

[CAT2016]

Hey MarcelBeug you are an absolute champion!!! Your formula was the one that worked best without any errors so a huge THANK YOU!!!!!! I don't really understand what you wrote (complete novice here) but it worked so I am happy with that!

Thanks again for all those that replied to my question you are all pretty good too

 

[Aladin Akyurek]

Hey MarcelBeug you are an absolute champion!!! Your formula was the one that worked best without any errors so a huge THANK YOU!!!!!! I don't really understand what you wrote (complete novice here) but it worked so I am happy with that!

Thanks again for all those that replied to my question you are all pretty good too

Thanks for skipping... But the formula should be:

=SUMPRODUCT($C$10:$C$154,--(MOD(ROW($C$10:$C$154)-ROW($C$10)+1,6)=0))

for reasons of robustness.