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

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

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

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

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)

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

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,

Nice. May suggest using MOD simplifies it a bit.

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

Jack Sons
The Netherlands


"Dav" <[email protected]> schreef in bericht
news:[email protected]...
>
> 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
>

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

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