如何在Excel中按特定间隔（行1、4、8…）应用公式

Hey Shozib, great question! Let's break this down clearly since you mentioned two slightly different scenarios: targeting rows 1, 4, 8, ... and operating with an "interval of 3". I'll cover both so you can pick what fits your actual needs.

Scenario 1: Apply Formula to Exact Rows (1, 4, 8, ...) #

If you need to target specific, non-uniform rows, here are two simple methods:

Method 1: Direct Row Check with OR #

Suppose you want to calculate SUM(B:C) in column A for those rows. In cell A1, enter this formula and drag it down the column:

=IF(OR(ROW()=1, ROW()=4, ROW()=8), SUM(B:C), "")

• The ROW() function gets the current row number.
• OR checks if the row matches any of your target numbers.
• If true, it runs your formula; if false, it returns an empty cell.

Method 2: Use a List of Target Rows (Scalable) #

If you have many target rows, list them in a separate range (e.g., D1:D3 with values 1, 4, 8) and use COUNTIF to check membership:

=IF(COUNTIF($D$1:$D$3, ROW())>0, SUM(B:C), "")

Just add more row numbers to column D later, and the formula will automatically include them—no need to edit the formula itself.

Scenario 2: Fixed Interval of 3 (Rows 1, 4, 7, 10, ...) #

If you meant a consistent interval of 3 (every 3rd row starting at 1), use the MOD function to detect the pattern:

=IF(MOD(ROW()-1, 3)=0, SUM(B:C), "")

• ROW()-1 adjusts the row number to start at 0 (so row 1 becomes 0, row 4 becomes 3, etc.).
• MOD(..., 3)=0 checks if the adjusted number is divisible by 3—this hits exactly rows 1, 4, 7, and so on.

Bonus: Dynamic Array Formula (Excel 365/2021) #

If you're using a modern Excel version, skip dragging the formula down with this dynamic array formula (enter it once in A1, and it fills automatically):

=IF(MOD(SEQUENCE(ROWS(A:A))-1, 3)=0, SUM(B:C), "")

Quick Manual Method: Batch Enter Formula #

If you prefer not to use conditional formulas:

1. Hold Ctrl and click on each target cell (e.g., A1, A4, A8) to select them all.
2. Type your formula (e.g., =SUM(B:C)) in the formula bar.
3. Press Ctrl+Enter—the formula will be applied to all selected cells instantly.

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