如何在电子表格中对无限列隔3单元格求和（球队账务场景）

Hey there! Let's get your football team's spreadsheet sorted so you don't have to update that SUM formula every time you add a new match. Based on your setup—where Aidan's balance values sit in every 3rd column starting at D3 (like D3, G3, J3, etc.)—here are a few flexible, expandable solutions:

Option 1: Use INDEX + SEQUENCE (for fixed 3-column intervals) #

This formula dynamically counts how many balance columns exist and sums all of them automatically, even when you add new matches:

=SUM(INDEX(Sheet1!$3:$3, 1, SEQUENCE(ROUNDUP(COUNTA(Sheet1!$3:$3)/3, 0), 1, 4, 3)))

Breakdown: #

• COUNTA(Sheet1!$3:$3) counts all non-empty cells in Aidan's row (row 3)
• ROUNDUP(..., 0) adds a safety net to account for any partial column sets (though your setup uses every 3rd column, this ensures we don't miss any values)
• SEQUENCE(..., 1, 4, 3) generates a list of column numbers starting at 4 (column D) with a step of 3 (so 4,7,10,...)
• INDEX pulls the values from those target columns in row 3, then SUM adds them up

Option 2: Use SUMPRODUCT (for concise interval matching) #

If you prefer a shorter formula, this uses column number math to target every 3rd column starting at D:

=SUMPRODUCT(Sheet1!$3:$3*(MOD(COLUMN(Sheet1!$3:$3)-4, 3)=0))

Breakdown: #

• COLUMN(Sheet1!$3:$3) gets the column number for every cell in row 3
• MOD(COLUMN()-4, 3)=0 checks if the column is 4 (D),7(G),10(J), etc. (subtracting 4 makes the first target column equal to 0, which is divisible by 3)
• SUMPRODUCT multiplies each cell by 1 (if it's a balance column) or 0 (if not), then sums the valid values

Option 3: Use FILTER (if your balance columns have clear headers) #

If your Sheet1 has headers like "Match 1 Balance", "Match 2 Balance" for these columns, this is the most readable option—it sums any cell in row 3 where the header ends with "Balance":

=SUM(FILTER(Sheet1!$3:$3, RIGHT(Sheet1!$1:$1, 7)="Balance"))

Breakdown: #

• RIGHT(Sheet1!$1:$1,7) grabs the last 7 characters of each header in row 1
• FILTER keeps only the cells in row 3 where the header ends with "Balance"
• SUM adds those filtered values together

All three formulas will automatically include new balance columns when you add more matches—no manual formula updates needed! Pick the one that fits your sheet's structure best.

内容的提问来源于stack exchange，提问作者codebytom