Excel Sum Every Other Column: Formulas and Examples

Formulas & functions · Updated November 18, 2026

To sum every other column in an Excel row, use =SUMPRODUCT((MOD(COLUMN(B2:K2)-COLUMN(B2),2)=0)*B2:K2). With numbers in B2:K2, this adds B2, D2, F2, H2, and J2; change the starting cell or the range to suit your layout.

The quick formula and what it selects

The formula uses the first cell in the range as its starting point. In B2:K2, the first included cell is B2, then Excel skips one column and includes D2, then F2, H2, and J2. K2 is not included because the alternating pattern ends on J2 in this range.

For a concrete example, suppose B2, D2, F2, H2, and J2 contain 10, 20, 30, 40, and 50. The formula returns 150. Values in C2, E2, G2, I2, and K2 do not affect that result. The formula calculates from the cell positions, not from whether a value is odd or even.

The example assumes the values are in B2:K2 and that the first cell, B2, should be included. The formula works in Microsoft 365 for Windows, and the same formula can be used in Excel for Mac and Excel for the web. If your Excel uses semicolons as argument separators because of regional settings, replace the formula’s commas where applicable; this particular formula has no function arguments separated by commas.

How the alternating-column test works

COLUMN(B2:K2) produces the column numbers for the cells in the range. Subtracting COLUMN(B2) makes the first position zero, the next one, and so on. The sequence is therefore 0, 1, 2, 3, continuing across the range.

MOD(...,2)=0 checks which of those positions have a remainder of zero when divided by two. That picks positions 0, 2, 4, 6, and 8: the first, third, fifth, seventh, and ninth cells in the range. The condition becomes a set of TRUE and FALSE values, which Excel uses to include the matching numeric cells in the sum.

The SUMPRODUCT function combines that position test with the values in the range and adds the selected results. This is why the formula is more flexible than typing a list of cell references: extend the range and the alternating pattern continues automatically.

Start with the second column instead

If the first cell in your range should be skipped, change the test from zero to one. With values in B2:K2 and a desired starting point of C2, use:

=SUMPRODUCT((MOD(COLUMN(B2:K2)-COLUMN(B2),2)=1)*B2:K2)

This selects C2, E2, G2, I2, and K2. For example, if those cells contain 100, 200, 300, 400, and 500, the result is 1,500. The starting reference inside the formula remains B2 because it defines position zero; changing the comparison to 1 selects the next position in the alternating sequence.

This distinction matters when a report has alternating categories, such as budget and actual figures, or quantity and price columns. Decide which category should be included, then set the first selected position accordingly. Do not assume that even-numbered values or even-numbered worksheet columns are what the formula means by every other column.

Choose between SUMPRODUCT and listing cells

For a short, fixed range, an explicit SUM formula is often easiest to read and audit. With values in B2:K2 and B2, D2, F2, H2, and J2 as the desired cells, enter:

=SUM(B2,D2,F2,H2,J2)

It returns 150 for the example values above. This is a good choice when the range will not change and you want the included cells to be visible in the formula. Its drawback is that you must edit the formula if columns are inserted, removed, or added to the calculation.

Use SUMPRODUCT when the range may grow, when you want the pattern to be obvious, or when you need to copy a consistent formula. Explicit references can also be easier for a colleague to inspect, while SUMPRODUCT avoids manually maintaining a long list. Neither method is universally better; the best choice depends on how often the worksheet layout changes.

Sum selected columns across several rows

The first SUMPRODUCT example is designed for one row. To calculate a result for each record, put the formula in a result column and copy it down. If the values for the next record are in B3:K3, the relative references adjust to that row when the formula is copied, selecting B3, D3, F3, H3, and J3.

For a single total covering several rows, it can be simplest to add the selected column ranges directly. With data in rows 2 through 10 and columns B through K, and with B, D, F, H, and J included, use:

=SUM(B2:B10,D2:D10,F2:F10,H2:H10,J2:J10)

This totals every cell in those five columns across the stated rows. It is explicit and easy to verify, though you must add a range if the worksheet gains another selected column. Another practical method is to calculate the alternating-column sum on each row in a helper column and then sum those row results. That makes it easier to compare or troubleshoot individual records.

Adjust the range without changing the pattern

Replace B2:K2 with the actual row range and update the first-cell reference inside both COLUMN expressions to match the range’s left edge. For example, if the data occupies C5:L5 and C5 is the first column to include, the formula is:

=SUMPRODUCT((MOD(COLUMN(C5:L5)-COLUMN(C5),2)=0)*C5:L5)

Here the included cells are C5, E5, G5, I5, and K5. Keeping the range and its starting reference aligned is important. If the range starts in C5 but the formula still subtracts COLUMN(B5), the positions shift and the result may select the opposite set of columns.

Also check the final column. If the range contains an even number of cells, the two alternating groups have the same number of positions. If it contains an odd number, the group beginning with the first cell has one more selected position. This is expected, not an error; the formula selects based on the pattern from the range’s left edge.

Common problems and checks

  • Wrong starting column: Confirm whether the first cell in the range should be included. Use the comparison to zero for the first, third, fifth positions, or one for the second, fourth, sixth positions.
  • Misaligned references: The first reference inside COLUMN should identify the leftmost cell in the data range. Keep the tested range and the range being summed the same size.
  • Unexpected totals: Test the formula on a short row with easy-to-check values, such as 10, 100, 20, 200, 30. The first-position version should return 60; the second-position version should return 300.
  • Numbers stored as text: A cell that looks numeric may be text imported from another system. Convert such entries to numbers if the result is missing expected amounts or behaves unexpectedly. Also keep labels and notes out of the cells intended to contain numeric data.
  • Inserted columns: Inserting a column can change which values occupy alternating positions. Recheck the intended pattern after editing the layout, especially if the formula is meant to select categories rather than simply every second physical column.
  • Different worksheet rows: A formula copied down normally shifts its row references. If you need a fixed row or range, use dollar signs carefully, but avoid locking the row when each copied formula should calculate a different record.

When this method is useful

Alternating-column sums are common in worksheets that place two related measures side by side: monthly actual and forecast values, paired quantity and revenue fields, or values for two scenarios. Summing every other column can provide a quick subtotal for one measure without rearranging the source data.

However, a positional formula depends on the layout staying consistent. If columns may be moved or new categories added, consider whether a more structured table would be clearer. For example, storing a category name such as Actual or Forecast in a separate field lets you filter or summarize by category rather than relying on every other physical column. For a small fixed report, the formula is convenient; for a growing dataset, organizing data in rows with descriptive headers can make later analysis more reliable.

Frequently Asked Questions

Can Excel sum every other column automatically?
Yes. SUMPRODUCT can test each column’s position and add the selected cells, as in the opening formula. The range and its starting cell determine which alternating group is included.

How do I sum the second, fourth, and sixth columns in a range?
Use the same range but change the MOD test from =0 to =1. The zero version selects positions 1, 3, and 5 when counted for a person; the one version selects positions 2, 4, and 6.

Does the formula work if some cells are blank?
Blank cells contribute zero to a sum, so they do not need to be filled with zeros. Check that the cells intended to contain amounts are numeric, particularly when data has been pasted or imported.

Can I use this formula down a column instead?
The same position-based idea can be adapted to a vertical range by using ROW rather than COLUMN. For a horizontal question, COLUMN is the appropriate function; for a vertical range, build the test from the row positions and ensure the reference starts at the top cell.

Why is my result adding the other set of columns?
The selected set is relative to the first cell in the range. Check that the range begins where you think it does and that the MOD comparison is zero for the first position or one for the next position.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.