This example will explain how to create an array formula that sums every nth row in Excel. We will show the formula using n = 3 as an example, but you can do it for any other number.
- Function ROW (ROW) returns the row number in which the cell is located.
=ROW(A1)=СТРОКА(A1) - Function MODE (MOD) displays the remainder of the division. For example:
- for the first row, the result is 1, and 1 is divisible by 3 (0 times) with a remainder of 1.
- for the third row, the result is 3, and 3 is divisible by 3 (exactly 1 time) with a remainder of 0.
- As a result, the formula returns 0 for every third row.
=MOD(ROW(A1),3)=ОСТАТ(СТРОКА(A1);3)
Note: Change 3 to 4 to sum every fourth line, change to 5 every fifth, and so on.
- Slightly modify the formula as shown below:
=MOD(ROW(A1),3)=0=ОСТАТ(СТРОКА(A1);3)=0 - To get the sum of the product of these two ranges (FALSE = 0, TRUE = 1), use the function SUM (SUM) and finish entering the formula with a keyboard shortcut Ctrl + Shift + Enter.
=SUM(A1:A9*(MOD(ROW(A1:A9),3)=0))=СУММ(A1:A9*(ОСТАТ(СТРОКА(A1:A9);3)=0))
Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.
Explanation:
- The product of these two ranges (an array of constants) is stored in Excel’s memory, not in the worksheet cells.
- The array of constants looks like this: {0;0;5;0;0;66;0;0;21}.
- This array of constants is used as an argument to the function SUM (SUM), giving a result of 92.