2. The MOD function finds the remainder of a division. For example, for the first row, MOD(1,3) = 1 because 1 divided by 3 equals 0 remainder 1. For the third row, MOD(3,3) = 0 because 3 divided by 3 equals 1 remainder 0 The result is that the formula returns 0 for every 3rd row.
Note: change 3 to 4 to sum every 4th row, to 5 to sum every 5th row, etc.
3. Slightly change the formula as shown below.
4. To get the sum of the product of these two ranges (FALSE = 0, TRUE = 1), use the SUM function and finish by pressing CTRL + SHIFT + ENTER.
Note: The formula bar indicates this is an array formula by enclosing it in curly braces {}. Don’t type these yourself. They will disappear when you edit the formula.
Explanation: The product of these two ranges (array constants) is stored in Excel’s memory, not in a range. The array constant looks like this.
{0;0;5;0;0;66;0;0;21}
This array constant is used as a parameter to the SUM function, which returns 92.
