Values Adding

[Jane]

I tried the formulare

= sumproduct(--(mod(a1:a30,2)=1),a1:a30).

It seems that it works only every two lines. It doesn't work when I change the every line to 3, 4, 5 and etc.

Thanks!

[Bob Phillips]

I tried the formulare

= sumproduct(--(mod(a1:a30,2)=1),a1:a30).

It seems that it works only every two lines. It doesn't work when I change the every line to 3, 4, 5 and etc.

Thanks!

Did you use


=sumproduct(--(mod(a1:a30,3)=1),a1:a30)
=sumproduct(--(mod(a1:a30,4)=1),a1:a30)
=sumproduct(--(mod(a1:a30,5)=1),a1:a30)
etc.

[Jane]

Hello, Xld:

Yes, I tried the following ways. Did I do something wrong?


=sumproduct(--(mod(a1:a30,3)=1),a1:a30)
=sumproduct(--(mod(a1:a30,4)=1),a1:a30)
=sumproduct(--(mod(a1:a30,5)=1),a1:a30)
etc.

Thank you very much!

[Bob Phillips]

Did I do something wrong?

Hi Jane,

No that is what I did.

If you put 1,2,3,4,5,6,7,8,9,10 in a1:a10, what do you get for each of those 3 formulae?

[Jane]

Hi, Xld:

I attached a spreadsheet for your review. I don't know which step I did is wrong.

Thank you for your help.

Jane

[Bob Phillips]

I attached a spreadsheet for your review. I don't know which step I did is wrong.

Hi Jane,

This is going to be tricky to explain, so bear with me.

The original formula is

= sumproduct(--(mod(row(a1:a30),2)=1),a1:a30).
which sums every other row, starting at the first ODD row in the range. Note here that the first row in the range is an odd numbered row.

You have shifted the range to C8:C18, note that the first row is now EVEN numbered row. So the formula

=SUMPRODUCT(--(MOD(ROW($C$8:$C$18),3)=0),$C$8:$C$18)
does not start at the first row in that range, but the first odd row. And it gets more complex with the other MODs.

Solution?

You need to set the start point relative to the first row in the range

=SUMPRODUCT(--(MOD(ROW($C$8:$C$18),3)=MOD(ROW($C$8),3)),$C$8:$C$18)
=SUMPRODUCT(--(MOD(ROW($C$8:$C$18),4)=MOD(ROW($C$8),4)),$C$8:$C$18)
=SUMPRODUCT(--(MOD(ROW($C$8:$C$18),5)=MOD(ROW($C$8),5)),$C$8:$C$18)

But don't even ask what happens if you want the first row to be the second in the range!

[Jane]

Hi, Xld:

Thank you very much! It works now.

Jane