# How to sum every other column in Excel

Test each column's position with MOD: `=SUMPRODUCT((MOD(COLUMN(B2:G2)-COLUMN(B2)+1,2)=0)*B2:G2)` adds the 2nd, 4th and 6th columns.

**Excel:** `=SUMPRODUCT((MOD(COLUMN(B2:G2)-COLUMN(B2)+1,2)=0)*B2:G2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Region | Q1 plan | Q1 actual | Q2 plan | Q2 actual | Q3 plan | Q3 actual | Actual total |
| East | 52000 | 49870 | 54000 | 55320 | 56000 | 53980 |  |
| West | 61000 | 63450 | 63000 | 61210 | 65000 | 66740 |  |
| North | 38000 | 36120 | 39000 | 40550 | 40000 | 39875 |  |
| South | 44000 | 45230 | 45000 | 43960 | 46000 | 47115 |  |

Formula in H2, filled to H5: `=SUMPRODUCT((MOD(COLUMN(B2:G2)-COLUMN(B2)+1,2)=0)*B2:G2)`

Result: $159,170; $191,400; $116,545; $136,305

## How it works

`COLUMN(B2:G2)-COLUMN(B2)+1` numbers the columns 1 to 6. MOD with 2 is 0 for columns 2, 4 and 6, the actuals, and SUMPRODUCT adds those values. Filling down moves the row while the pattern stays the same.

## Other ways

- `=SUMIF($B$1:$G$1,"*actual",B2:G2)`: When the headers say what each column is, SUMIF across the header row is clearer and doesn't depend on the column order.

## Common mistakes

- Inserting a column between the quarters breaks the every-other pattern. Summing by header text keeps working.
- Use `=0` for even positions and `=1` for odd ones. Getting it backwards adds the plans instead of the actuals.

## Questions

**How do I sum every third column?**

Change the 2 to a 3: `MOD(COLUMN(B2:G2)-COLUMN(B2)+1,3)=0`.

**How do I sum the plan columns instead?**

Test for odd positions: `=SUMPRODUCT((MOD(COLUMN(B2:G2)-COLUMN(B2)+1,2)=1)*B2:G2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-every-other-column