Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


3 Handy Approaches to Skip Columns in Excel Formula

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.

Handy Approaches to Skip Columns in Excel Formula


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 K5 cell.
  • Then, write down the following formula.
=SUMPRODUCT(C5:H5*MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2))

 Utilizing SUMPRODUCT, MOD, and COLUMN Functions to Skip Columns in Excel Formula

Formula Breakdown

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


Similar Readings


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 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.
=SUM(IF(MOD(COLUMN($C5:$H5),2)=0,$C5:$H5,0))

 Incorporating SUM, COLUMN, and IF Functions to Skip Columns in Excel Formula

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 cells C5 to H5. It yields 0 in all other cases.
  • SUM(IF(MOD(COLUMN($C5:$H5),2)=0,$C5:$H5,0)): The SUM function will then be called with the return value from 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 K5 cell.
  • Besides, drag the Fill Handle tool from the K5 cell to the K10 cell now.

Incorporating SUM, COLUMN, and IF Functions to Skip Columns in Excel Formula

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 Methods)


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.

Applying VBA Code to Skip Columns in Excel Formula

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

Applying VBA Code to Skip Columns in Excel Formula

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 K5 cell first.
  • 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 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 (8 Easy Methods)


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. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo