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 Excel: Forumlas.
Add Comment
1 Answer(s)

RE: Excel Formula to find Sum of Even Numbers and Odd Numbers

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.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.