PDA

View Full Version : sum every n-th row



lior03
11-20-2007, 07:14 AM
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
11-20-2007, 07:20 AM
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
11-20-2007, 07:34 AM
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
11-20-2007, 09:08 AM
{=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
11-21-2007, 04:13 AM
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