sum every nth cell in a row
 

Sorry Posted in wrong group earlier

Hi

I am trying to adapt Bob Phillips formula

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))

I have cells, e5,e6,e7,e8,e9,e10,e11,e12,e13,e14,e15, etc
I want to sum, e5,e9,e13 etc
I will need to adapt this in another cell to add, e6,e10,e14 etc and
therefore need to understand the N + M value

I have entered the formula below in cell c5, but get 0. Have entered as
array formula
{=SUMPRODUCT((E5:AY5)*(MOD(ROW(E5:AY5)-ROW(B2),4)+1=F5))}

What am I doing wrong

Help appriciated

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200803/1

sum every nth cell in a row
 

need to understand the N + M value

N = divisor used in the MOD function
M = mod

For example:

Using cell E5:

MOD(ROW(E5),n)=m

MOD(ROW(E5),4)=1

I want to sum, e5,e9,e13 etc

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100)

I will need to adapt this in another cell to add, e6,e10,e14 etc

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=2),E5:E100)


--
Biff
Microsoft Excel MVP


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:81808c2606b7d@uwe...
Sorry Posted in wrong group earlier

Hi

I am trying to adapt Bob Phillips formula

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))

I have cells, e5,e6,e7,e8,e9,e10,e11,e12,e13,e14,e15, etc
I want to sum, e5,e9,e13 etc
I will need to adapt this in another cell to add, e6,e10,e14 etc and
therefore need to understand the N + M value

I have entered the formula below in cell c5, but get 0. Have entered as
array formula
{=SUMPRODUCT((E5:AY5)*(MOD(ROW(E5:AY5)-ROW(B2),4)+1=F5))}

What am I doing wrong

Help appriciated

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200803/1

sum every nth cell in a row
 

Keep getting #Value! error

copied and pasted in c5, and entered with shift+ctl+ent

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100)

Any ieas?



T. Valko wrote:
need to understand the N + M value

N = divisor used in the MOD function
M = mod

For example:

Using cell E5:

MOD(ROW(E5),n)=m

MOD(ROW(E5),4)=1

I want to sum, e5,e9,e13 etc

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100)

I will need to adapt this in another cell to add, e6,e10,e14 etc

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=2),E5:E100)

Sorry Posted in wrong group earlier

[quoted text clipped - 18 lines]

Brian

--
Message posted via http://www.officekb.com

sum every nth cell in a row
 

entered with shift+ctl+ent

Doesn't need to be array entered but that won't cause an error. Just ENTER
will work.

Keep getting #Value! error

Are there any #VALUE! errors already in the range?

Here's a small sample file that demonstrates this.

xSumNth.xls 14kb

http://cjoint.com/?dvxr6QVxsY


--
Biff
Microsoft Excel MVP


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:81816da88aaa2@uwe...
Keep getting #Value! error

copied and pasted in c5, and entered with shift+ctl+ent

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100)

Any ieas?



T. Valko wrote:
need to understand the N + M value

N = divisor used in the MOD function
M = mod

For example:

Using cell E5:

MOD(ROW(E5),n)=m

MOD(ROW(E5),4)=1

I want to sum, e5,e9,e13 etc

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=1),E5:E100)

I will need to adapt this in another cell to add, e6,e10,e14 etc

=SUMPRODUCT(--(MOD(ROW(E5:E100),4)=2),E5:E100)

Sorry Posted in wrong group earlier

[quoted text clipped - 18 lines]

Brian

--
Message posted via http://www.officekb.com

sum every nth cell in a row
 

thanks T.Valko

After looking at the link, realised, I gave you wrong info, my range was in a
one row, different columns

changed "row" to "column" and presto worked !

Thanks for your patients

brian

Thanks fo
T. Valko wrote:
entered with shift+ctl+ent

Doesn't need to be array entered but that won't cause an error. Just ENTER
will work.

Keep getting #Value! error

Are there any #VALUE! errors already in the range?

Here's a small sample file that demonstrates this.

xSumNth.xls 14kb

http://cjoint.com/?dvxr6QVxsY

Keep getting #Value! error

[quoted text clipped - 30 lines]

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200803/1

sum every nth cell in a row
 

Glad you got it straightened out. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:818ab09a2bc1f@uwe...
thanks T.Valko

After looking at the link, realised, I gave you wrong info, my range was
in a
one row, different columns

changed "row" to "column" and presto worked !

Thanks for your patients

brian

Thanks fo
T. Valko wrote:
entered with shift+ctl+ent

Doesn't need to be array entered but that won't cause an error. Just ENTER
will work.

Keep getting #Value! error

Are there any #VALUE! errors already in the range?

Here's a small sample file that demonstrates this.

xSumNth.xls 14kb

http://cjoint.com/?dvxr6QVxsY

Keep getting #Value! error

[quoted text clipped - 30 lines]

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200803/1