Formula for adding non-consecutive cells across entire row

[nevans1982]

Hoping someone can help; I'm trying to automatically add non-consecutive cell values together and am currently using the old fashioned approach of SUM(cell1, cell7, cell13...). The pattern remains the same (i.e. wanting to add values 6 cells apart) but as I add more data, I would like to automatically add the new data to the SUM total. There must be a way of creating a pattern whereby Excel only SUMs every 6th cell but my formula work is pretty basic... Note that I will want the formula to work for the same pattern (i.e. every 6th cell) but starting at cell 2 instead of cell 1 so I would need to define a start and end cell for the pattern to commence and end.

Hopefully you can see what I'm getting at!

[#2]

@nevans1982 

If to sum every 6th cell in first row, it could be

=SUMPRODUCT(IF(MOD(COLUMN(INDEX(1:1,0,FirstColumn):INDEX(1:1,0,LastColumn))-FirstColumn,Period)=0,INDEX(1:1,0,FirstColumn):INDEX(1:1,0,LastColumn),0))

where FirstColumn, LastColumn and Period are named cells with parameters. Similar if every 6th cell in the column.

[#3]

To add the values in Column A, but which includes only A2, A8, A14, A20, and so forth, try this formula:
=SUMPRODUCT((MOD(ROW(Range)+
4,6)=0)*
Range)
Note that Range is a defined name that refers to the values in Column A, starting in A2.

[#4]

@Twifoo , that works only if there are no texts in between.

[#5]

@nevans1982

If you are willing to use a VBA code solution, here is a UDF (user defined function) that you can use...

Function SumAcross(StartCell As Range, SkipAmount As Long) As Double
  Dim C As Long, LastCol As Long
  Application.Volatile
  LastCol = Cells(StartCell.Row, Columns.Count).End(xlToLeft).Column
  For C = StartCell.Column To LastCol Step SkipAmount
    SumAcross = SumAcross + Cells(StartCell.Row, C).Value
  Next
End Function

The first argument to the function is a reference to the starting cell and the second argument is the amount of cells to skip over while traveling from the starting cell across that starting cell's row.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SumAcross just like it was a built-in Excel function. For example,

=SumAcross(A2,6)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

[#6]

Let's expand the function I posted above to add an optional third argument... the stop cell. If omitted, the last column will be calculated by the code and will be the last column with data on the start cell's row; but if a cell reference is provided, then its column will be used as the last column to calculate up to. Here is the code...

Function SumAcross(StartCell As Range, SkipAmount As Long, Optional StopCell As Variant) As Double
  Dim C As Long, LastCol As Long
  Application.Volatile
  If IsMissing(StopCell) Then
    LastCol = Cells(StartCell.Row, Columns.Count).End(xlToLeft).Column
  Else
    LastCol = StopCell.Column
  End If
  For C = StartCell.Column To LastCol Step SkipAmount
    SumAcross = SumAcross + Cells(StartCell.Row, C).Value
  Next
End Function