sum on every 17th column

[andrewb90]

Hello all,

I found this formula online, and It's just not quite working as intended.

=SUMPRODUCT((MOD(COLUMN($D4:$II4),17)=0)*($D4:$II4))

I'm wanting it to cum the value of every 17th cell, however when I change it to 17 it counts the 17th column first(q) then every 17, But I'm wanting the first column D counted then every 17th from there. How do I modify this?

 

[ricardov]

Since you're working on an array, you can simply minus the position of your starting column before it steps into every 17th position.

[COLOR=#333333]=SUMPRODUCT((MOD(COLUMN($D4:$II4)-COLUMN($D4),17)=0)*($D4:$II4))[/COLOR]

Also, you can always use Formula > Formula Auditing (Group) > Evaluate Formula to check and see how you're data is evaluated and do the necessary corrections.

If this answers your question, please take the time to hit the Like button on this response.

 

[Ingolf]

Hi,

Try this:

=SUMPRODUCT((MOD(COLUMN($D4:$II4)-3,17)=0)*($D4:$II4))

 

[ricardov]

Also, it might help to add, since the "SUMPRODUCT" can be very misleading, you can also use

=SUM((MOD(COLUMN($D4:$II4)-COLUMN($D4),17)=0)*($D4:$II4))

and instead of Enter, use CTRL+SHIFT+Enter... this makes it an array formula and provides the underlying formula with brackets around it.

{=SUM((MOD(COLUMN($D4:$II4)-COLUMN($D4),17)=0)*($D4:$II4))}

Advantage is - when you or somebody else reads the formula, it's more properly described as SUM which is your intention.

 

[ricardov]

I think it should be 4, and not 3... which is essentially the column index of D. Andrewb90 wanted to start the sum at column D, so the first array value should equal 0.

Hi,

Try this:

=SUMPRODUCT((MOD(COLUMN($D4:$II4)-3,17)=0)*($D4:$II4))

 

[andrewb90]

I'm not sue why, but this isn't working either

[COLOR=#333333]=SUMPRODUCT((MOD(COLUMN($D4:$II4)-COLUMN($D4),17)=0)*($D4:$II4))[/COLOR]

To sum up what I am doing: I am trying to add: D4,T4,AJ4,AZ4, etc

 

[ricardov]

D to T is 16 counts. Formula you need is:

=SUMPRODUCT((MOD(COLUMN($D4:$II4)-COLUMN($D4),16)=0)*($D4:$II4))

 

[Ingolf]

I think it should be 4, and not 3... which is essentially the column index of D.
=SUMPRODUCT((MOD(COLUMN($D4:$II4)-3,17)=0)*($D4:$II4)).

Andrewb90 wanted to start the sum at column D, so the first array value should equal 0.

If substract 4, then we have MOD ({0,1,2,3 ...}, 17) and MOD (0,17) = 0

 

[andrewb90]

16... I feel dumb now. thanks!

 

[Ingolf]

I'm not sue why, but this isn't working either

To sum up what I am doing: I am trying to add: D4,T4,AJ4,AZ4, etc

If you want to start with D4 then next number (after 16 cells) will be U4 , AL4 not T4, AJ4...