In an Excel spreadsheet, we may apply the SUM function to add some lists of cell values, but sometimes we need to add some specific cells, skipping some other cells. There is no standard formula to skip columns in Excel. In this article, we will show you how to skip columns in an Excel formula.

## How to Skip Columns in Excel Formula: 3 Handy Approaches

In the following methods, you will learn to skip columns in the **Excel** formula by utilizing the **SUMPRODUCT, MOD, **and **COLUMN functions**, incorporating **the SUM, COLUMN, **and * the IF functions*, and applying

**the VBA code**. Letâ€™s suppose we have a sample data set.

### 1. Utilizing SUMPRODUCT, MOD, and COLUMN Functions to Skip Columns

This function offers a more adaptable way to skip columns in **Excel** formulas in **Excel** worksheets by utilizing the **SUMPRODUCT**, **MOD**, and **COLUMN **functions. The general syntax of the **SUMPRODUCT**, **MOD**, and **COLUMN **functions is given below.

**Formula Syntax of the SUMPRODUCT Function**

`=SUMPRODUCT(array1,[array2],...)`

**Arguments of the SUMPRODUCT Function**

**Array1**represents the first input to an array whose elements you want to multiply and then add.**Array2**represents the second input to an array, whose elements you want to multiply and then add (optional).

**Formula Syntax of the MOD Function**

`=MOD(number, divisor)`

**Arguments of the MOD Function**

**The number**indicates the second input in which you want to find the remainder.**A divisor**is a number by which you want to divide the number.

**Formula Syntax of the COLUMN Function**

`=COLUMN(reference)`

**Arguments of the COLUMN Function**

**Reference**shows the reference to a cell or range of cells.

**Step 1:**

- Firstly, select the
cell.*K5* - Then, write down the following formula.

`=SUMPRODUCT(C5:H5*MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2))`

**Formula Breakdown**

- Firstly, we use three more functions here including
**SUMPRODUCT**,**MOD**, and**COLUMN**functions. **MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2)):**returns the referenced cell’s column number. The formula is then modified by subtracting*The COLUMN function*and then adding This is required because adding a new column before the reference prevents you from changing its value.*COLUMN(C5)*returns the remainder amount after dividing the number by the divisor. Then we declare divisor**The MOD function****2**.**SUMPRODUCT(C5:H5*MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2)):**then returns the array’s total value. Here, we combine the result of*The SUMPRODUCT function***the MOD function**with the range of cellsto*C5*. It ignores the value when*H5***the MOD function**gives a value of**0**, but adds the value when**the MOD function**returns a value of**1**.

**Step 2:**

- Now, hit
**ENTER**to apply the formula. - Here, you will see the total sales of the first salesman in the
cell.*K5* - Now, use the
tool and drag it down from the*Fill Handle*cell to the*K5*.*K10*

**Step 3:**

- Finally, you will get the sum of all the sales over several months for all the salesmen.
- Here, we skip the
,*D*, and*F*columns to add the sales value for every salesman.*H*

**Read More: **How to Skip a Column When Selecting in Excel

### 2. Incorporating SUM, COLUMN, and IF Functions to Skip Columns in Excel

By combining the **SUM**, **COLUMN**, and **IF **functions, this method provides a more flexible way to skip columns in Excel formulas on worksheets. The general syntax of the **SUM,**Â **COLUMN, **and **IF **functions are given below.

**Formula Syntax of the SUM Function**

`=SUM(number1,[number2],...)`

**Arguments of the SUM Function**

**Number1**conveys the first number you want to add.**Number2**conveys the second number you want to add (optional).

**Formula Syntax of the COLUMN Function**

`=COLUMN(reference)`

**Arguments of the COLUMN Function**

**Reference**shows the reference to a cell or range of cells.

**Formula Syntax of the IF Function**

`=IF(logical_test, [value-if_true], [value_if_false])`

**Arguments of the IF Function**

**Logical_Test i**ndicates a quantity or logical statement that can be determined to be**TRUE**or**FALSE**.**Value_if_true**demonstrates the value that will be returned if the logical test evaluates to**TRUE**.**Value_if_false**demonstrates the value that will be returned if the logical test evaluates to**FALSE**.

**Step1:**

- First, select the
cell.*K5* - Then, write down the following formula.

`=SUM(IF(MOD(COLUMN($C5:$H5),2)=0,$C5:$H5,0))`

** **

**Formula Breakdown**

- The first three functions we employ are
**SUM**,**COLUMN**, and**IF**functions. **MOD(COLUMN($C5:$H5),2)=0:****The COLUMN function**returns the referenced cell’s column number.**The MOD function**, which divides by**2**, is then added. After an integer has been divided by a divisor,**the MOD function**returns the remainder.**IF(MOD(COLUMN($C5:$H5),2)=0,$C5:$H5,0):**Put the previous**MOD**and**COLUMN**functions as**the IF function**criterion after that. According to this formula, if the remainder is**zero**, move to the cellsto*C5*. It yields*H5***0**in all other cases.**SUM(IF(MOD(COLUMN($C5:$H5),2)=0,$C5:$H5,0)):**will then be called with the return value from*The SUM function***the IF function**. All columns that meet the requirement will be added by**the SUM function**. Then, passing over other columns, it returns the sum of those columns.

**Step 2:**

- Now, press
**ENTER**to apply the formula. - As a result, you will see the entire sales of the first salesman in theÂ
cell.*K5* - Besides, drag the
tool from the*Fill Handle*cell to the*K5*cell now.*K10*

**Step 3:**

- Finally, you will receive the total of all sales made by all salespeople over a period of time.
- Here, to add the sales value for each salesman, we exclude the
,*C*, and*E*columns in this case.*G*

**Read More: **How to Skip Every Other Column Using Excel Formula

### 3. Applying VBA Code to Skip Columns in Excel

**VBA** is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the **Alt + F11** keyboard shortcut, you can launch the **VBA editor**. In the last section, we will generate a **VBA code **that makes it very easy to skip columns in the **Excel **formula**.**

**Step 1:**

- Firstly, we will open the
*De**v*tab.*eloper* - Then, we will select the
command.*Visual Basic*

**Step 2:**

- Here, the
window will open.*Visual Basic* - After that, from the
option, we will choose the new*Insert*to write a*Module***VBA code**.

** **

**Step 3:**

- Now, paste the following
**VBA code into**the*Module*.

```
'Specifying the function name
Function SkipColumns(WorkRange As Range, interval As Integer) As Double
'Declaring variables
Dim ar As Variant
Dim x As Double
'Setting value for x and ar
x = 0
ar = WorkRange.Value
'Using For Loop
For i = interval To UBound(ar, 2) Step interval
x = x + ar(1, i)
Next
SkipColumns = x
End Function
```

**VBA Code Breakdown**

- Firstly, we specify a function in the worksheet as double using the below statement

`Function SkipColumns(WorkRange As Range, interval As Integer) As Double`

- Secondly, we declare variables as

```
Dim ar As Variant
Dim x As Double
```

- Thirdly, we set
**x**to zero and**ar**to a range.value that gives back a value that corresponds to a given range of values as

```
x = 0
ar = WorkRange.Value
```

- Now, we apply a For Loop setting interval using this statement as

```
For i = interval To UBound(ar, 2) Step interval
x = x + ar(1, i)
Next
```

- After that, we return the value for the specified function as

`SkipColumns = x`

- Finally, we end the function of the VBA macro as

`End Function`

**Step 4:**

- At the start of this section, choose the
cell first.**K5** - Then, type the following formula.

`=SkipColumns(C5:H5,2)`

**Step 5:**

- Now, click
**ENTER**to apply the formula. - Consequently, you will get the entire sales of the first salesman in the
cell.*K5* - Besides, drag the
tool from the*Fill Handle*cell to the*K5*cell now.*K10*

**Step 6:**

- Finally, you will observe the total of all sales made by all salespeople over a period of time.
- In this instance, to add the sales value for each salesman, we exclude the
,*C*, and*E*columns in this case.*G*

** **

**Read More: **How to Skip Cells in Excel Formula

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice it by yourself.

## Conclusion

In this article, we’ve covered** 3 **handy methods to** skip columns **in** the Excel formula.** We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

## Related Articles

- Excel Formula to Skip Rows Based on Value
- How to Skip Hidden Cells When Pasting in Excel
- Skip Cells When Dragging in Excel
- How to Skip Blank Rows Using Formula in Excel
- How to Skip Lines in Excel
- Skip to Next Cell If a Cell Is Blank in Excel
- Skip to Next Result with VLOOKUP If Blank Cell Is Present

**<< Go Back to Skip CellsÂ Â | Excel Cells | Learn Excel**