Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |