SUM(OFFSET)?

[Mike Fogleman]

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

Any ideas?
TIA Mike F

 

[Bob Umlas]

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

 

[Frank Kabel]

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

 

[Arvi Laanemets]

Hi

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


Arvi Laanemets

 

[Guest]

One way

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

Regards,

Peo Sjoblom

 

[Mike Fogleman]

That results in #VALUE!

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

 

[Jason Morin]

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

 

[Guest]

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

Regards,

Peo Sjoblom

 

[Guest]

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


Regards,

Peo Sjoblom

 

[Mike Fogleman]

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)

 

[hrlngrv]

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)))