Jul 12 2019 12:46 AM
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!
Jul 12 2019 01:51 AM - edited Jul 12 2019 01:57 AM
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.
Jul 12 2019 07:17 AM
Jul 12 2019 07:25 AM
@Twifoo , that works only if there are no texts in between.
Jul 13 2019 07:57 PM - edited Jul 13 2019 07:59 PM
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.
Jul 14 2019 08:11 PM
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