A quicker way?

[UpTheArgyle]

Hi

I have a spreadsheet with a lot of lines of data - i now decide that in Line 1 i want to see a total of the values of column E, but i want to add up every 18th cell starting at E38.
Is there a quicker way to get this formula other than clicking in each 18th cell, pressing the + key and going onto the next 18th !?

=E38+E56+E74+E92+E110+E128+E146+E164+E182+E200+E218+E236+E254+E272+E290+E308+E326+E344+E362+E380+E398+E416+E434+E452+E470

I need this down to line 1000

thanks

 

[Steve=True]

You can do this with a MOD formula.

Unfortunately i am just learning it.

An MVP can answer this for you.

 

[JackDanIce]

I'd use a macro. Try:

Sub Sum18s()
Dim i As Long
Dim form As String
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
form = "E"
i = 38
 
Do
    form = form & i & "+E"
    i = i + 18
Loop Until i > 1000
 
form = Left(form, Len(form) - 2)
Range("E1").Formula = "=SUM(" & form & ")"
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[MrKowz]

Try:

=SUM(IF(MOD(ROW(E38:E1000),18)=2,E38:E1000,"")

Confirm entry with CTRL+SHIFT+ENTER to store this as an array entered formula. You will know it is entered properly when you see {brackets} around the formula.

 

[njimack]

Is this what you're after? (confirm with ctrl+shift+enter)
=SUM(IF(MOD(ROW(E38:E1000)-2,18)=0,E38:E1000))

 

[Norie]

This formula seems to work, but it's probably hightly inefficient and maybe even wrong.

Anyway here it is:

=SUMPRODUCT(--(MOD(ROW(E38:E1000),18)=2), E38:E1000)

This uses the row number but it might be worth checking to see if you can base the criteria for summing on something else.

PS I bet this can probably be done with SUMIF, or the new SUMIFS.

 

[GK039]

Hi,

In cell F38 enter the formula:

=IF(MOD(ROW(),18)=2,E38,0)

and copy down as much as you like

Then sum based on column F

George

Hi

I have a spreadsheet with a lot of lines of data - i now decide that in Line 1 i want to see a total of the values of column E, but i want to add up every 18th cell starting at E38.
Is there a quicker way to get this formula other than clicking in each 18th cell, pressing the + key and going onto the next 18th !?

=E38+E56+E74+E92+E110+E128+E146+E164+E182+E200+E218+E236+E254+E272+E290+E308+E326+E344+E362+E380+E398+E416+E434+E452+E470

I need this down to line 1000

thanks