Formula for adding non-consecutive cells across entire row

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.

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.

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.