SUM every Nth row formula example
In real-time, Excel users rarely use the MOD function on their own. It is often used in combination with other functions that require the remainder value.
We will use the MOD function in combination with the SUMPRODUCT function and ROW function for this example.
Our example data set is as follows:
Each level represents the total sales of three different shops in a city. We want to SUM every fourth row in our data set containing the total to reach a final value.
You can do that manually by selecting adjacent cells, but with an extensive data set, that is not a feasible option.
So, the formula we will use for this operation is
Yeah, I know it looks scary 🥴
But don’t worry – we will go through each part of this formula in detail. And complete it step by step.
Let’s first discuss the SUMPRODUCT function.
The SUMPRODUCT function multiplies different ranges together and returns the SUM of the products as a result.
It is an array formula. And we will use its first argument =SUMPRODUCT (array1) to combine other functions.
Next comes the MOD function. The “(MOD” function contains two arguments, and the first one is the ROW function.
(ROW($B$2:$B$13) contains the cell references of the data set whose rows we want to total. Since we won’t have to change this, we will make it an absolute range.
The ROW function will return the ROW number of each row. And it acts as the number argument in the MOD formula (MOD(ROW($B$2:$B$13)-1.
Next, we add a -1 to the ROW function. This is because we want to total every fourth row in our data set. But the fourth row of the spreadsheet and the one containing the Sales total are different.
So to avoid confusion, we will take away one row by entering -1 in the formula. And Excel MOD function will then count each fourth row.
The second argument of the MOD function is “4” – which is the divisor.