Sum Every 3 Cells in Excel: Formula & Examples

This calculator helps you sum every 3 cells in a row or column, a common need when dealing with grouped data like weekly totals, quarterly figures, or repeated measurements. You enter a list of numbers and the calculator returns the sum of the 1st, 4th, 7th, … cells (and optionally the 2nd, 5th, 8th, … and 3rd, 6th, 9th, … groups). The first output is the sum of the first group (cells 1, 4, 7, …), which is what most people mean by “sum every 3 cells.�

Formula

To sum every 3rd cell starting at cell 1, use the array formula:

=SUM(IF(MOD(ROW(range)-ROW(firstCell),3)=0, range)) (entered with Ctrl+Shift+Enter in older Excel) or the modern dynamic array version: =SUM(FILTER(range, MOD(SEQUENCE(ROWS(range),1,0),3)=0))

For a row instead of a column, replace ROW with COLUMN and SEQUENCE(ROWS(range),1,0) with SEQUENCE(1,COLUMNS(range),0).

Variables:

• range – the cells to sum (e.g., A1:A30)
• firstCell – the first cell of the range (e.g., A1)
• MOD(…,3) – remainder after dividing by 3; 0 means every 3rd cell

In Google Sheets, the simpler formula is: =SUMIF(ARRAYFORMULA(MOD(ROW(A1:A30)-ROW(A1),3)),0,A1:A30)

Worked Example

Using the default list: 5, 12, 7, 8, 3, 9, 15, 6, 4, 11, 2, 10

Group 1 (cells 1,4,7,10): 5 + 8 + 15 + 11 = 39
Group 2 (cells 2,5,8,11): 12 + 3 + 6 + 2 = 23
Group 3 (cells 3,6,9,12): 7 + 9 + 4 + 10 = 30

The calculator’s first output shows 39 for group 1, matching the manual calculation.

When to Use This Pattern

Summing every 3 cells is useful when your data is grouped in threes but you only want one of the three values per group. Common scenarios:

• Weekly data where you want every 3rd week (e.g., monthly reporting from weekly figures)
• Survey responses where each question has 3 sub-ratings and you need the first sub-rating
• Experimental trials repeated 3 times, but you only want the first trial of each set
• Financial data with columns like “budgetâ€�, “actualâ€�, “varianceâ€� – you might want only the “actualâ€� columns

How to Choose the Starting Point

The “every 3 cells� pattern depends entirely on where you start. If your data begins in cell A1, then:

• Starting at A1 gives you cells 1,4,7,10,…
• Starting at A2 gives you cells 2,5,8,11,…
• Starting at A3 gives you cells 3,6,9,12,…

In the calculator, these correspond to group1, group2, and group3. If you want to sum every 3rd cell but starting from a different row (e.g., A2), adjust the formula’s offset: =SUM(IF(MOD(ROW(range)-ROW(firstCell),3)=1, range)) for starting at the second cell.

Common Mistakes and Fixes

• Off-by-one errors – Using MOD(…,3)=1 when you meant =0. Check which cells you actually want.
• ROW vs COLUMN – Using ROW for a horizontal range gives wrong results. Use COLUMN for rows.
• Non-array entry – In older Excel, forgetting Ctrl+Shift+Enter returns a single value or #VALUE!. Use SUMPRODUCT to avoid array entry: =SUMPRODUCT((MOD(ROW(range)-ROW(firstCell),3)=0)*range)
• Blank cells – Blank cells are treated as 0, which is usually fine, but if you have text, the sum will be #VALUE!. Use SUMIFS or FILTER to ignore text.
• Hidden rows – The formula includes hidden rows. If you need to skip hidden rows, use SUBTOTAL or AGGREGATE instead.

Alternatives to the MOD Formula

If you prefer not to use array formulas, you can use SUMPRODUCT as shown above, or use a helper column. For example, in column B, enter =MOD(ROW()-1,3) and then use SUMIF(B:B,0,A:A). This is easier for beginners and works in all versions of Excel.

In Google Sheets, you can also use QUERY: =SUM(QUERY(A1:A,”select A where row(A) mod 3 = 1″)) (adjust the offset).

For very large datasets, consider using a pivot table with a grouping field, but that requires adding a helper column to identify groups.

Limitations of the Formula

The formula assumes your data is contiguous and starts at a known cell. If your data has gaps or you need to sum every 3rd cell across multiple non-adjacent ranges, you’ll need to adjust the range or use multiple formulas. Also, the formula sums only numeric values; text or errors in the range will cause problems.

For rows, the formula works but you must change ROW to COLUMN and adjust the SEQUENCE accordingly. The calculator above handles columns only, but the logic is identical for rows.

Frequently Asked Questions

How do I sum every 3rd cell in Excel without array formulas?

Use SUMPRODUCT: =SUMPRODUCT((MOD(ROW(A1:A30)-ROW(A1),3)=0)*A1:A30). This works without Ctrl+Shift+Enter and is more robust.

What if my data is in a row instead of a column?

Replace ROW with COLUMN: =SUMPRODUCT((MOD(COLUMN(A1:Z1)-COLUMN(A1),3)=0)*A1:Z1). The calculator above is column-oriented, but the same principle applies.

Can I sum every 3rd cell starting from the 2nd or 3rd cell?

Yes, change the MOD remainder. For starting at the 2nd cell, use =1; for the 3rd, use =2. In the calculator, the outputs group2 and group3 give you those sums directly.

How do I sum every 3rd cell in Google Sheets?

Use =SUMIF(ARRAYFORMULA(MOD(ROW(A1:A30)-ROW(A1),3)),0,A1:A30). Or use FILTER: =SUM(FILTER(A1:A30, MOD(SEQUENCE(ROWS(A1:A30),1,0),3)=0)).

Why does my formula return #VALUE!?

This usually happens when the range contains text or errors. The MOD function works with numbers, but SUM over text fails. Use SUMIF or FILTER to exclude non-numeric cells, or clean your data.

Can I sum every 3 cells across multiple sheets?

Yes, but you need to reference each sheet separately. For example: =SUM(Sheet1!A1:A30, Sheet2!A1:A30) won’t work directly for every 3rd cell. You’d need to combine the ranges with VSTACK (in newer Excel) or use a 3D reference with SUMPRODUCT, which is complex. A simpler approach is to sum each sheet separately and then add the results.

Related

• Samsung Galaxy S21 Press Renders Leak?
• Affordable Sports Car: Porsche Handling, Lexus Reliability
• Control Lights with Phone: How It Works

in list | Numbers | 5, 12, 7, 8, 3, 9, 15, 6, 4, 11, 2, 10 | 0
out group1 | Sum of cells 1,4,7,... | - | sum(list[0::3]) | 2
out group2 | Sum of cells 2,5,8,... | - | sum(list[1::3]) | 2
out group3 | Sum of cells 3,6,9,... | - | sum(list[2::3]) | 2