In Excel, sometimes you need to deal with some complex tasks like to sum or highlight cells that have even or odd numbers, sum every Nth rows or column in a range, validate cells to enter numbers with even or odd or without fraction and many more. To solve these tricky problems you need to use Excel **MOD** function. Though Excel MOD function is basically designed to get the division remainder, it is far more useful to solve many complex problems like above while working with data.

Excel **MOD function** returns the remainder after a number is divided by a divisor. In Excel, it is categorized as a **Math & Trig** Function and it returns a numeric value.

Syntax of MOD function is:

**MOD( number, divisor )**

Where,

**Number:** A numeric value whose remainder is needed.

**Divisor:** The number used to divide the number parameter.

- If the divisor is 0, then it will return the
**#DIV/0**error because a number cannot be divided by 0. - If any of two arguments is text value then it will return
**#VALUE**error because it handles only numeric values.

For example, if you divide the numbers by divisors, then it will return remainder values by using MOD function as follows;

It is very rare that MOD function is used to get the only remainder of division in Excel. MOD function is quite often used in formulas to make calculations based on the remainder of the divisor in Excel. In this article, you will learn how to use MOD function in Excel to solve various advanced problems to reach at solutions easily.

__Count cells that contain odd or even numbers__

Excel does not have any built-in function to perform such count but MOD function can do this in the formula using **MOD **and** SUMPRODUCT** functions. As you know that if a value is divided by 2 and remainder is 0 then it is **even** value, but if the remainder is 1 then the value is **odd**. Using MOD function you can perform this check that which cell has even or odd value in a selected range of cells, and SUMPRODUCT function counts the total cells where this condition is TRUE.

Suppose you have numbers in a range of cells **A2:A8** and you need to count cell that have Even or Odd numbers using the following formulas;

__Count cells with Even numbers:__

**=SUMPRODUCT(--(MOD(A2:A8,2)=0))**

__Count cells containing Odd numbers:__

**=SUMPRODUCT(--(MOD(A2:A8,2)=1))**

Here, MOD function divides each cell’s value in range by 2, and if remainder in the result is 0 then it is even valued and if the remainder is 1 then it is an odd number. Excel returns a series of TRUE and FALSE where these logical tests are met or not in the above formulas. Double dashes **(–)** outside the MOD function converts series of TRUEs and FALSEs into 1s and 0s and SUMPRODUCT function sums this series of 1s and 0s to give you the total count of cells.

__Sum every other row or column__

Based on even and odd numbers logic, you can sum every other row or column in a selected range of cells using MOD, ROW or COLUMN and SUMPRODUCT functions. In this case, you can sum every even or odd row or column number in the selected range of cells.

Suppose you have a range of cells with a number and you want to sum every even or odd row then formulas will be as follows;

__Sum every Even row:__

**=SUMPRODUCT(--(MOD(ROW(A2:A8),2)=0),A2:A8)**

__Sum every Odd row:__

**=SUMPRODUCT(--(MOD(ROW(A2:A8),2)=1),A2:A8)**

Here, MOD function will perform check where row number in the selected range is even or odd, and SUMPRODUCT function will sum the values of even or odd row numbers in the above formulas.

__Sum every Nth row or column__

There is no built-in function in Excel to sum values of every 2^{nd}, 3^{rd} or 4^{th} etc. row or column in a range. But, using MOD function with the combination of ROW or COLUMN and SUMPRODUCT functions in formulas you can perform such calculations easily by following this generic formulas syntax;

*SUMPRODUCT(--(MOD(ROW(range)-ROW(first_cell)+1, n)=0),range)*

OR

*SUMPRODUCT(--(MOD(COLUMN(range)-COLUMN(first_cell)+1, n)=0),range)*

Where ** n** is the Nth row or column to be summed in provided

**range**

Suppose you have monthly and quarterly sales figures of salespersons and you want to sum the sales of all quarters of each salesperson. So using MOD function you can perform this calculation as per the following formula.

**=SUMPRODUCT(--(MOD(COLUMN(B2:Q2)-COLUMN(B2)+1,4)=0),B2:Q2)**

In this example sales figures are provided column wise and quarterly sales figure of every salesperson is available in **4 ^{th}** column of the selected range for each salesperson. So, you need to sum every

**4**column figure in each range.

^{th}

__Highlight odd and even numbers__

Using conditional formatting custom formula you can conditionally highlight odd and even number in a range by using MOD function.

Select the range of cells that you need to conditionally highlight. Go to **Home tab > Style section> click on Conditional Formatting > New Rule > Use formula to determine which cells to format**

You need to add the following two formulas in **Conditional Formatting Rule Manager** dialog box for even and odd numbers and select separate formatting style for each.

The formula for cells with **even** numbers:

**=MOD($A2,2)=0**

Format > Fill = **Green**

The formula for cells with **odd** numbers:

**=MOD($A2,2)=1**

Format > Fill = **Gold**

## Leave a Comment