# Excel VBA Video Training / EXCEL DASHBOARD REPORTS # Excel Function That Sums Every Nth Cell In a Specified Range

Back to: Excel Custom Function/Formulas . Got any Excel/VBA Questions? Free Excel Help

This Custom Function will sum every nth row or cell in the specified range. How about a free Handy Excel Functions Add-in?

The Code

```Function SumEveryNth2007(rRange As Range, lNth As Long, Optional SumEveryNthRow As Boolean) As Single
Dim rCell As Range

'''''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid.com'''''''''''''''''''
'Sums every nth cell.
'If more than 1 column, summation order is left-to-right and top-to-bottom
'unless SumEveryNthRow=TRUE
'Function very inefficient, rRange should be as small as possible.
'Most efficient method is DSUM, shown here
'http://www.ozgrid.com/Excel/sum-every-2nd-nth-cell.htm
'''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
If SumEveryNthRow = False Then
With WorksheetFunction
For Each rCell In rRange
lStep = lStep + 1
If lStep Mod lNth = 0 Then sTot = .Sum(rCell, sTot)
Next rCell
End With

SumEveryNth2007 = sTot
Else
SumEveryNth2007 = Evaluate("=SUMPRODUCT((MOD(ROW(" & strAddress & ")," & lNth & ")=0)*(" & strAddress & "))")
End If

End Function```

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

=SumEveryNth2007(\$A\$1:\$A\$10,2)
This would sum every 2nd cell in the range \$A\$1:\$A\$10

=SumEveryNth2007(\$A\$1:\$B\$10,TRUE)
This would sum every 2nd row in the range \$A\$1:\$B\$10

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.