sum every n-th row

[lior03]

hello all
the following formula sum every second row of a range

=SUM(IF(MOD(ROW(A2:A15),2)=1,A2:A15,0))

my vba equavalent is:

Function sumodds(r As Range)
Dim cell As Range
Dim lastcell As Range
Dim i As Integer
For Each cell In r
Set lastcell = ActiveCell.End(xlDown).Select
For i = ActiveCell To lastcell Step 2
sumodds = sumodds + cell.Value
Next
Next
End Function


can anyone help?

[rory]

Something like:
Function SumEveryN(rngIn As Range, lngStep As Long) As Double
Dim lngCounter As Long
For lngCounter = 1 To rngIn.Columns(1).Cells.Count Step lngStep
SumEveryN = SumEveryN + Val(rngIn.Cells(lngCounter, 1).Value)
Next lngCounter
End Function

[rory]

Actually this may be better:
Function SumEveryNRows(rngIn As Range, lngStep As Long) As Double
Dim rngRow As Range
For Each rngRow In rngIn.Rows
If (rngRow.Row - rngIn.Row + 1) Mod lngStep = 0 Then _
SumEveryNRows = SumEveryNRows + Application.Sum(rngRow)
Next rngRow
End Function

[unmarkedhelicopter]

{=SUM(IF(MOD(ROW(A2:A15),2)=1,A2:A15,0))}
why not just enter that as an array formula ?
i.e. press Ctrl-Shft-Enter

[Krishna Kumar]

Hi,

Function SumEveryN(r As Range, n As Long, Optional RC As Boolean) As Double
Dim RowCol As String
If RC Then RowCol = "COLUMN" Else RowCol = "ROW"
SumEveryN = Evaluate("sumproduct(--(mod(" & RowCol & "(" & r.Address(0, 0) & _
")-min(" & RowCol & "(" & r.Address(0, 0) & "))+1," & n & _
")=0)," & r.Address(0, 0) & ")")
End Function

for every second row: =sumeveryn(A1:A30,2)

for every second column: =sumeveryn(E2:Z2,2,1)

HTH