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

 

[MarcelBeug]

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.

@Aladin: so the difference is * in my formula, versus ,-- in your formula.
I understand both, but I wonder why ,-- would be more robust. Care to comment? Thanks.

 

[Aladin Akyurek]

@Aladin: so the difference is * in my formula, versus ,-- in your formula.
I understand both, but I wonder why ,-- would be more robust. Care to comment? Thanks.

Enter a row in front of the current first data row and observe what happens.

 

[MarcelBeug]

Enter a row in front of the current first data row and observe what happens.

Thanks, my formula keeps summing element 1, 7 etcetera, and yours element 6, 12 etcetera of the range, no matter how many rows are inserted.
I guess you are mistaking with the original formulas in post #1.

 

[Aladin Akyurek]

Thanks, my formula keeps summing element 1, 7 etcetera, and yours element 6, 12 etcetera of the range, no matter how many rows are inserted.
I guess you are mistaking with the original formulas in post #1.

If every 7h is intended;

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

 

[CAT2016]

Does something happen if you add further rows?

 

[Aladin Akyurek]

Does something happen if you add further rows?

The formulas of post #6 (and of course post #14) won't break if you add rows in front of the current data area. You probably mean something else. Care to elaborate?

 

[MarcelBeug]

The formulas of post #6 (and of course post #14) won't break if you add rows in front of the current data area.

Neither will the formula in post #4.

 

[CAT2016]

Does something happen if you add further rows?

Neither will the formula in post #4.

Quote Originally Posted by Aladin Akyurek View Post

The formulas of post #6 (and of course post #14) won't break if you add rows in front of the current data area.
Neither will the formula in post #4.


I really don't know very much about Excel, but I am assuming, when you talk about adding rows in front of the current data areas, that the formula will no longer work?? If I am going to add any data it will be further down the spreadsheet, but then I would just amend the formula to reflect this, correct?

When I say I don't know much about Excel I really mean it so if you can explain things like you are talking to a three year old that would be great that is if you want to.

 

[Aladin Akyurek]

Quote Originally Posted by Aladin Akyurek View Post

The formulas of post #6 (and of course post #14) won't break if you add rows in front of the current data area.
Neither will the formula in post #4.


I really don't know very much about Excel, but I am assuming, when you talk about adding rows in front of the current data areas, that the formula will no longer work?? If I am going to add any data it will be further down the spreadsheet, but then I would just amend the formula to reflect this, correct?

When I say I don't know much about Excel I really mean it so if you can explain things like you are talking to a three year old that would be great that is if you want to.

1. Are we summing every 7th?

2. What is the name of the sheet housing the data?