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 row or column in a range, validate cells to enter numbers with even or odd or without a fraction, and many more. To solve these tricky problems you can use the **MOD** function.

## The 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. The **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.

### The MOD formula

The syntax of the 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 the MOD function as follows;

It is rare that the MOD function is used to get the only remainder of division in Excel. The 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 the MOD function in Excel to solve various advanced problems to reach at solutions easily.

__Count cells that contain odd or even numbers__

__Count cells that contain odd or even numbers__

Excel does not have any built-in function to perform such a count, but the MOD function can do this in the formula using the **MOD **and** SUMPRODUCT** functions. If a value is divided by 2 and the remainder is 0, then it is an **even** value, but if the remainder is 1 then the value is **odd**. Using the MOD function you can perform this check on which cell has even or odd values in a selected range of cells, and the 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 a 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__

__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__

__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 the 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 for salespersons and you want to sum the sales of all quarters of each salesperson. So using the 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__

__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**

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

## Leave a Comment

1

Comment awaiting moderation

1

Comment awaiting moderation