Sum function that add every 13th row...

G

Greg

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!! :)
 
T

Toppers

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
 
G

Greg

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!!
 
H

Herbert Seidenberg

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

Toppers

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
 
D

Dav

If you wanted to just put a formula in to calculate the sum of ever
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 coun
this row. this number divided by 13 is equal to int(number/13) every 1
rows, but as you want to start with row 18 we have to subtract
(18-5=13!)

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

Regards

Da
 
J

Jack Sons

Dav,

Nice. May suggest using MOD simplifies it a bit.

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

Jack Sons
The Netherlands
 
F

Flintstone

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
 
Top