advanced sum, need help

[cazlo]

hi...

I have huge date in a sheet and i want to sum certain cells all multiply by a 37 cells that mean

I need to sum a1 + a38 + a75 + a112 ++++ a18456 what I have to do?

 

[Brian from Maui]

Try

=SUMPRODUCT(--(MOD(ROW(A$1:A$18456)-CELL("Row",A$1)+0,37)=0),A$1:A$18456)

 

[AlphaFrog]

Try this...
=SUMPRODUCT(--(MOD(ROW($A$1:$A$18456)-1,37)=0),$A$1:$A$18456)


Alternatively; the SUM Function ignores Text and blanks. So if there are no numbers in between, an easy solution would be to just
=SUM(A1:A18456)

 

[Snakehips]

cazio,

Welcome to MrExcel.

Try something like....

=SUMPRODUCT(A1:A20000*(MOD(ROW(A1:A20000),37) = 1))

Hope that helps.

 

[Scott Huish]

You shouldn't need CELL, ROW should work just fine:

=SUMPRODUCT(--(MOD(ROW($A$1:$A$18456)-ROW(A1),37)=0),A1:A18456)

 

[cazlo]

sorry plz I don't understand and it now works

this's the cells I want to sum

J30+J67+J104+J141+J178+J215+J252+J289+J326+J363+J400+J437+J474+J511+J548 + .. j18502

 

[Scott Huish]

=sumproduct(--(mod(row($j$30:$j$18502)-row(j30),37)=0),j30:j18502)

 

[cazlo]

thank youuuuuuu man, you are awesome