SUM(OFFSET)?
 

I want to sum C4 and each cell every 51st row down. C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

=SUMPRODUCT((OFFSET(C4,0,0,10000,1)*(MOD(ROW(4:100 03),51)=4)))

"Mike Fogleman" wrote in message
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down. C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

Hi
try
=SUMPRODUCT(--(MOD(ROW(C4:C1000)-4,51)=0),C4:C1000)

--
Regards
Frank Kabel
Frankfurt, Germany
"Mike Fogleman" schrieb im Newsbeitrag
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down. C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

Hi

On fly
=SUMPRODUCT(--(MOD((ROW(C4:C1000)+47);51)=0);C4:C1000)


Arvi Laanemets


"Mike Fogleman" wrote in message
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down. C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

One way

=SUMPRODUCT(--(MOD(ROW(C4:C500),51)=4),C4:C500)

Regards,

Peo Sjoblom

"Mike Fogleman" wrote:

I want to sum C4 and each cell every 51st row down. C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

That results in #VALUE!
"Bob Umlas" wrote in message
...
=SUMPRODUCT((OFFSET(C4,0,0,10000,1)*(MOD(ROW(4:100 03),51)=4)))

"Mike Fogleman" wrote in message
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down.
C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

Just 2 other ways:

=C4+SUMPRODUCT(SUM(OFFSET(C4,ROW(1:1000)*51,)))
=C4+SUMPRODUCT(SUM(INDIRECT("C"&ROW(1:100)*51+4)))

HTH
Jason
Atlanta, GA

-----Original Message-----
I want to sum C4 and each cell every 51st row down.
C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F


.

If it does you must have text in some of the cells in question

Regards,

Peo Sjoblom

"Mike Fogleman" wrote:

That results in #VALUE!
"Bob Umlas" wrote in message
...
=SUMPRODUCT((OFFSET(C4,0,0,10000,1)*(MOD(ROW(4:100 03),51)=4)))

"Mike Fogleman" wrote in message
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down.
C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

Of course if you have text values in there you can use any of the other 3
formulas you were given


Regards,

Peo Sjoblom

"Peo Sjoblom" wrote:

If it does you must have text in some of the cells in question

Regards,

Peo Sjoblom

"Mike Fogleman" wrote:

That results in #VALUE!
"Bob Umlas" wrote in message
...
=SUMPRODUCT((OFFSET(C4,0,0,10000,1)*(MOD(ROW(4:100 03),51)=4)))

"Mike Fogleman" wrote in message
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down.
C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

Thanks, that works great. And I see how to adapt it to other cells, eg...
=SUMPRODUCT(--(MOD(ROW(C5:C1000)-5,51)=0),C5:C1000)

"Frank Kabel" wrote in message
...
Hi
try
=SUMPRODUCT(--(MOD(ROW(C4:C1000)-4,51)=0),C4:C1000)

--
Regards
Frank Kabel
Frankfurt, Germany
"Mike Fogleman" schrieb im Newsbeitrag
news:XgDAd.39842$k25.6833@attbi_s53...
I want to sum C4 and each cell every 51st row down. C4+C55+C106+C157+....
etc.

Any ideas?
TIA Mike F

Jason Morin wrote...
Just 2 other ways:

=C4+SUMPRODUCT(SUM(OFFSET(C4,ROW(1:1000)*51,)))
=C4+SUMPRODUCT(SUM(INDIRECT("C"&ROW(1:100)*51+4)) )
....

Or

=SUMPRODUCT(N(OFFSET(C4,(ROW(INDIRECT("1:1000"))-1)*51,0)))