Sum function that add every 13th row...
 

Is it possible to sum a column starting from row 18 and ending at row 226
only summing every 13th row. For example, sum the following rows
18,31,44,57,70 ... 226? Please help!!
Thanks in advance!! :)

Sum function that add every 13th row...
 

Sub Sum13

Dim sum as Double
Dim row as integer, col as integer

MySum =0
col=3 '<=== change to suit
For row=18 to 226 step 13
Mysum=Mysum+cells(row,col)
next row

msgbox Mysum

end Sub


HTH

"Greg" wrote:

Is it possible to sum a column starting from row 18 and ending at row 226
only summing every 13th row. For example, sum the following rows
18,31,44,57,70 ... 226? Please help!!
Thanks in advance!! :)

Sum function that add every 13th row...
 

That works ok but is there some kind of function, so I can place it a cell
and copy and paste it accross many cells? Please advise.
Thanks!!



"Toppers" wrote:

Sub Sum13

Dim sum as Double
Dim row as integer, col as integer

MySum =0
col=3 '<=== change to suit
For row=18 to 226 step 13
Mysum=Mysum+cells(row,col)
next row

msgbox Mysum

end Sub


HTH

"Greg" wrote:

Is it possible to sum a column starting from row 18 and ending at row 226
only summing every 13th row. For example, sum the following rows
18,31,44,57,70 ... 226? Please help!!
Thanks in advance!! :)

Sum function that add every 13th row...
 

Greg,

Range("D26").Value = Mysum <--- sub D26 for cell of choice

HTH


"Greg" wrote:

That works ok but is there some kind of function, so I can place it a cell
and copy and paste it accross many cells? Please advise.
Thanks!!



"Toppers" wrote:

Sub Sum13

Dim sum as Double
Dim row as integer, col as integer

MySum =0
col=3 '<=== change to suit
For row=18 to 226 step 13
Mysum=Mysum+cells(row,col)
next row

msgbox Mysum

end Sub


HTH

"Greg" wrote:

Is it possible to sum a column starting from row 18 and ending at row 226
only summing every 13th row. For example, sum the following rows
18,31,44,57,70 ... 226? Please help!!
Thanks in advance!! :)

Sum function that add every 13th row...
 

Here is a non-VBA solution, using functions.
Assume your data looks like this:
lista
1
2
3
4
....
299
300

i_val
13
st_row
18
end_row
226

listb
18
31
44
57
70
....
213
226

lista contains your input data, in this case numbers from 1 to 300.
i_val is the interval (every 13th number)
st_row is your starting row in lista (the 18th entry in lista)
end_row is your ending row in lista (lista can be longer than that)
listb is the output.
Give all the lists and constants the names indicated.
Use Insert Name Define
Also define these names:
no_rows Refers To =INT((end_row-st_row)/i_val+1)
seq Refers To
=(ROW(INDEX($A:$A,1):INDEX($A:$A,no_rows))-1)*i_val+st_row
Select the cells under listb and enter this array formula
(Ctrl+Shift+Enter)
=INDEX(lista,seq)
listb is no_rows deep.
The final answer is
=SUM(listb)

Sum function that add every 13th row...
 

In cell put =Sum13(a18:a226)

It will count every 13th row from start of given range.


Function Sum13(ByVal rng As Range)

Dim sum As Double
Dim row As Integer

Mysum = 0

For row = 1 To rng.Count Step 13
Mysum = Mysum + rng(row)
Next row

Sum13 = Mysum

End Function

"Greg" wrote:

That works ok but is there some kind of function, so I can place it a cell
and copy and paste it accross many cells? Please advise.
Thanks!!



"Toppers" wrote:

Sub Sum13

Dim sum as Double
Dim row as integer, col as integer

MySum =0
col=3 '<=== change to suit
For row=18 to 226 step 13
Mysum=Mysum+cells(row,col)
next row

msgbox Mysum

end Sub


HTH

"Greg" wrote:

Is it possible to sum a column starting from row 18 and ending at row 226
only summing every 13th row. For example, sum the following rows
18,31,44,57,70 ... 226? Please help!!
Thanks in advance!! :)

Sum function that add every 13th row...
 

If you wanted to just put a formula in to calculate the sum of every
13th row, you could use the following if your data is in column c.


=SUMPRODUCT((C18:C226)*((INT((ROW(C18:C226)-5)/13))=((ROW(C18:C226)-5)/13)))

row returns the row number
int truncates the number looses the decimal places

as you want every 13th row starting at row 18 and you wish to count
this row. this number divided by 13 is equal to int(number/13) every 13
rows, but as you want to start with row 18 we have to subtract 5
(18-5=13!)

Sumproduct sums when this is true so sums every 13th row!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=519879

Sum function that add every 13th row...
 

Dav,

Nice. May suggest using MOD simplifies it a bit.

=SUMPRODUCT((C18:C226)*(MOD(ROW(C18:C226)-5;13)=0))

Jack Sons
The Netherlands


"Dav" schreef in bericht
...

If you wanted to just put a formula in to calculate the sum of every
13th row, you could use the following if your data is in column c.


=SUMPRODUCT((C18:C226)*((INT((ROW(C18:C226)-5)/13))=((ROW(C18:C226)-5)/13)))

row returns the row number
int truncates the number looses the decimal places

as you want every 13th row starting at row 18 and you wish to count
this row. this number divided by 13 is equal to int(number/13) every 13
rows, but as you want to start with row 18 we have to subtract 5
(18-5=13!)

Sumproduct sums when this is true so sums every 13th row!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=519879

Sum function that add every 13th row...
 

Assuming your data is in the B column, try this.

=IF(MOD(ROW()-5,13)=0,B5,"")

Or

=IF(MOD(ROW()-5,13)=0,SUM(B5:IV5),"")

Matt


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=519879

Sum function that add every 13th row...
 

=SUMPRODUCT(--(MOD(ROW(A18:A226)-ROW(A18)+0,13)=0),A18:A226)

Greg wrote:
Is it possible to sum a column starting from row 18 and ending at row 226
only summing every 13th row. For example, sum the following rows
18,31,44,57,70 ... 226? Please help!!
Thanks in advance!! :)