Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Greg
 
Posts: n/a
Default 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!! :)


  #2   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default 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!! :)


  #3   Report Post  
Posted to microsoft.public.excel.misc
Greg
 
Posts: n/a
Default 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!! :)


  #4   Report Post  
Posted to microsoft.public.excel.misc
Dominic
 
Posts: n/a
Default 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!! :)


  #5   Report Post  
Posted to microsoft.public.excel.misc
Herbert Seidenberg
 
Posts: n/a
Default 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)



  #6   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default 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!! :)


  #7   Report Post  
Posted to microsoft.public.excel.misc
Dav
 
Posts: n/a
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Jack Sons
 
Posts: n/a
Default 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



  #9   Report Post  
Posted to microsoft.public.excel.misc
Flintstone
 
Posts: n/a
Default 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

  #10   Report Post  
Posted to microsoft.public.excel.misc
Aladin Akyurek
 
Posts: n/a
Default 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!! :)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a Custom Excel Function to Calculate Gini Coefficients [email protected] Excel Worksheet Functions 3 February 21st 06 11:15 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM


All times are GMT +1. The time now is 02:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"