Sum every nth column

How to sum every nth column in Excel

Learn how to sum every nth column (for example, every second, third, or fifth column) using the Excel SUM, FILTER, and SEQUENCE functions.

How to sum every nth column in Excel

Here are the steps to sum every nth column in Excel:

  1. Open Excel.
  2. Type =SUM(FILTER(range, MOD(SEQUENCE(1, COLUMNS(range)), n) = 0)).
  3. Press Enter.
  4. The formula will return the sum of every nth column in the specified range.

Example

I want to sum every 4th column using the range B3:J10 in the example.

Formula:

=SUM(FILTER(C3:J6, MOD(SEQUENCE(1, COLUMNS(C3:J6)), 4) = 0))

How to sum every nth column in Excel

Explanation

Evaluate the formula from the inside out:

  1. SEQUENCE(1, COLUMNS(range)): The SEQUENCE function generates an array from 1 up to the number of columns in the specified range. For example, if the range spans five columns, this will produce the array {1, 2, 3, 4, 5}.
  2. MOD(SEQUENCE(1, COLUMNS(range)), n) = 0: This part checks if each column number is a multiple of “n” (the interval you specify). The MOD function outputs zero for columns that meet this criterion, resulting in a logical array where TRUE marks every nth column and FALSE marks others.
  3. FILTER(range, …): The FILTER function uses this logical array to extract only those columns from a range that match the nth-column criterion.
  4. SUM(…): Finally, SUM adds up all the values from the filtered columns, giving you the sum of every nth column in the specified range.

Sum every nth column using SUM and INDEX

For non-Microsoft 365 users, we provide a workaround with the built-in SUM and INDEX functions. The goal is the same: sum every 4th column using a formula.

Formula:

=SUMPRODUCT((MOD(COLUMN(range) – COLUMN(range_start) + 1, n) = 0) * range)

The formula =SUMPRODUCT((MOD(COLUMN(range) – COLUMN(range_start) + 1, n) = 0) * range) calculates the sum of values at every “nth” column within a specified range.

Formula:

=SUMPRODUCT((MOD(COLUMN(C4:J6) – COLUMN(C4:C6) + 1, 4) = 0) * C4:J6)

Sum every nth column using SUMPRODUCT MODE and COLUMN

Here’s the explanation of how the formula works:

  1. COLUMN(range) – COLUMN(range_start) + 1: This part calculates the relative position of each column within the range, starting from 1. For instance, if range_start is B2 and range spans from B2:F2, it returns positions like 1, 2, 3, … for columns B, C, D, … respectively.
  2. MOD(… , n) = 0: The MOD function finds the remainder when each relative column position is divided by n. When the result equals 0, it identifies columns at every “nth” position within the range. For instance, if n = 2, this condition is TRUE for columns 2, 4, 6, etc.
  3. (MOD(…) = 0) * range: This part multiplies the Boolean array (TRUE/FALSE) by range, converting TRUE to 1 and FALSE to 0. Only the values in the “nth” positions remain because non-“nth” values are multiplied by 0.
  4. SUMPRODUCT: Finally, SUMPRODUCT adds up the selected “nth” values, providing the total.

Related formulas: