# Excel Formula to find Sum of Even Numbers and Odd Numbers

What is the Excel Formula to find sum of Odd or Even number in a Column

Expert Asked on July 18, 2015 in

## Steps to find Sum of Even/ Odd Numbers using Formula

1. Decide the Column to Sum the Even Numbers
2. Use MOD function to find Odd
3. Use SUMPRODUCT formula to find Total Sum of Odds
4. Find sum of total Numbers
5. Now subtract the sum of Odd numbers from Total
6. Here is the formula to Sum Even and Odd numbers

## Excel Formula to find Sum of Odd Numbers

Here is the formula to find sum of Odd numbers in Column A:

```=SUMPRODUCT(A:A,MOD(A:A,2))
```

### How this works?

• MOD(A:A,2) function will result 0 if Value is Even Number, 1 if a value if Odd number
• SUMPRODUCT will take the two arrays “A:A” and the resulting array of MOD(A:A,2)
• Now SUMPRODUCT will multiply each value in the column A with respective MOD function result (0 or 1)
• Ultimately this will multiply each number with 0 if it is Even, multiply with 1 if it is an Odd number.
• SUMPRODUCT will sum all the multiplied values, ultimately the sum value will be Sum of Odd numbers( as it is multiplied all even numbers with 0s, And all Odd numbers with 1s)

## Excel Formula to find Sum of Even Numbers

Now it is simple to find Even numbers, Right? We can simply sum all numbers and subtract the Sum of Odd numbers from the Total, will result the Sum of Even Numbers:

```=SUM(A:A)-SUMPRODUCT(A:A,MOD(A:A,2))
```

### How this works?

• SUM(A:A), will be the sum of the values in the Collumn A
• SUMPRODUCT(A:A,MOD(A:A,2)), will be the sum of the Odd numbers in the Columns
• SUM(A:A)-SUMPRODUCT(A:A,MOD(A:A,2)) , will be the Sum of All Values – Sum of Odd Numbers = Sum of Even Numbers
Keymaster Answered on July 18, 2015.
• ## Found this useful?

Please share using the share button above.