How to Skip Columns in Excel Formula (3 Easy Ways)

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

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

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

Arguments of the COLUMN Function

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

Step 1:

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

Step 2:

• Now, hit ENTER to apply the formula.
• Here, you will see the total sales of the first salesman in the K5 cell.
• Now, use the Fill Handle tool and drag it down from the K5 cell to the 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, F, and H columns to add the sales value for every salesman.

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

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

Arguments of the COLUMN Function

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

Formula Syntax of the IF Function

Arguments of the IF Function

• Logical_Test indicates 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 K5 cell.
• Then, write down the following formula.

Step 2:

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

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, E, and G columns in this case.

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 Developer tab.
• Then, we will select the Visual Basic command.

Step 2:

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write a 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

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

Dim ar As Variant
Dim x As Double

x = 0
ar = WorkRange.Value

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

SkipColumns = x

End Function

Step 4:

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

Step 5:

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

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, E, and G columns in this case.

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