• 462K Members
• 7,582 Online
• 559K Conversations

## Formula for adding non-consecutive cells across entire row

Occasional Visitor

# Formula for adding non-consecutive cells across entire row

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!

5 Replies

# Re: Formula for adding non-consecutive cells across entire row

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.

# Re: Formula for adding non-consecutive cells across entire row

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.

# Re: Formula for adding non-consecutive cells across entire row

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

# Re: 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.

# Re: Formula for adding non-consecutive cells across entire row

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
```
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies