This is where MOD gets really cool.
You can combine MOD with other functions to solve all sorts of problems. One of the most common is to perform some operation on every nth row or column.
On the second sheet of the workbook, we have several sales numbers for each month.
We can combine MOD with SUM and COLUMN to get the total sales for odd months, even months, every third month, and so on.
Here’s the formula we’re going to use to get the sum of sales for odd months:
Let’s start in the middle, with the COLUMN function.
COLUMN returns the column number for all the cells in the range. So for B2, it returns 2. For C2, 3. For D2, 4. And so on.
MOD, as we’ve seen, returns the remainder of a division equation. The number divided in this case is the column number of each cell, and the divisor is 2.
In essence, this returns a 0 for every even column (keep in mind that we’re using the even columns because January is in column 2).
The =0 is important here. This creates a logical statement. Whenever the MOD function returns a 0, we’ll have 0=0, which resolves to TRUE. Excel treats TRUE as 1.
When MOD returns 1, we’ll have 1=0, which is FALSE. FALSE is also treated as a 0.
The sum of each column is multiplied by that 1 or 0, and then added to the final total.
This is an array function, so to properly execute it, you’ll need to press Ctrl + Shift + Enter.
After that, take a deep breath—you have the sum of all the sales in odd months!
If you want to sum every third or fourth month, just change the divisor from 2 to 3 or 4.