Summing large number of non contiguous cells evenly distributed

[James D]

Hello!

Is there a way to enter a summing formula so that it looks at every 42nd
cell in a range of cell e6 thru e1602 and sums each cell to a grand total?

I could do =sum(e6+e48+e90............all the way to e1602, but with the
size of this sheet that would take me hours.....or a whole day. I don't have
that much time, and there are over 120 different columns of data I'll have to
do this for......

Thanks!

 

[Don Guillett]

Sub sumevery42ndcell()
lr = Cells(Rows.Count, "e").End(xlUp).Row
For i = 1 To lr Step 42 'test with smaller number
mysum = mysum + Cells(i, "e")
Next i
MsgBox mysum
End Sub

 

[HARSHAWARDHAN. S .SHASTRI]

Hello!

Is there a way to enter a summing formula so that it looks at every 42nd
cell in a range of cell e6 thru e1602 and sums each cell to a grand total?

I could do =sum(e6+e48+e90............all the way to e1602, but with the
size of this sheet that would take me hours.....or a whole day. I don't have
that much time, and there are over 120 different columns of data I'll have to
do this for......

Thanks!

 

[HARSHAWARDHAN. S .SHASTRI]

Hello!

Is there a way to enter a summing formula so that it looks at every 42nd
cell in a range of cell e6 thru e1602 and sums each cell to a grand total?

I could do =sum(e6+e48+e90............all the way to e1602, but with the
size of this sheet that would take me hours.....or a whole day. I don't have
that much time, and there are over 120 different columns of data I'll have to
do this for......

Thanks!

HI JAMES.D,
PL PUT THIS FORMULA =IF(MOD(ROW(),6)=0,E6,0) SAY IN CELL F6 AND COPY THE
FORMULA UPTO CELL F1602 .AND THEN IN CELL F1603, PUT THE FORMULA = SUM
(F6:F1602).YOU WILL GET THE REQUIRED RESULTS.

HARSHAWARDHAN.S.SHASTRI

INDIA

 

[Duke Carey]

=sumproduct(e6:e1602,--(mod(row(e6:e1602),42)=6))

 

[James D]

Thanks!!!!!
--
Thank you all for your help!

=sumproduct(e6:e1602,--(mod(row(e6:e1602),42)=6))