Sum only even numbers in a range with SUMPRODUCT

Sum only even numbers in a range with SUMPRODUCT

Suppose we want to sum only the even numbers in the range:
F3:F21

There are, at least, four possible solutions:

=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)/2*2)

=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)*1)

=SUMPRODUCT(F3:F21,--(MOD(F3:F21,2)=0))

=SUMPRODUCT(F3:F21,N(MOD(F3:F21,2)=0))

First Version:

=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)/2*2)

Explanation:

SUMPRODUCT multiplies 2 arrays (or more).

Each element in the first array is multiplied by its corresponding element in the second array, and the final result of the formula is the summation of all these multiplications (SUM of that PRODUCT = SUMPRODUCT).

The first array F3:F21 is the numbers in F3:F21.

The second array calculates only the even numbers in that range.

The expression: MOD(F3:F21,0)=0 checks each and every number in that range to see whether it is odd or even. The answer for each number is either TRUE or FALSE. But Excel cannot multiply numbers by a Boolean expression, so we need to convert the series of: FALSEs and TRUEs into 0’s and 1’s.
This can be done by multiplying each:
FALSE or TRUE with (2/2 = 1).

Multiplication of FALSE * 1 will yield: 0

Multiplication of TRUE * 1 will yield: 1

So, the second array becomes an array of alternate: 0 and 1.

By multiplying the first array (F3:F21) by the second array, only the second array’s elements which contain even numbers will be 1s. Thus, we’re multiplying and the summing only the even numbers and get the desired result

Second version:

 Multiplying the second array by 1:
=SUMPRODUCT(F3:F21,(MOD(F3:F21,2)=0)*1)

Third Version:

Adding -- before the second array to convert it to numbers:

Instead of multiplying the second array by 1 (or by: 2/2) we can 

simply add -- [two hyphens] before that array, in order to convert the Boolean values (TRUE or FALSE) into numbers (1 or 0):

=SUMPRODUCT(F3:F21,--(MOD(F3:F21,2)=0))

Fourth Version:

 Multiplying the second array by N (a function in Excel that converts Boolean values into number):

=SUMPRODUCT(F3:F21,N(MOD(F3:F21,2)=0))

Excel - A simple trick to sum only even numbers in a range

A simple trick to sum only the even numbers in a range

  An Array Formula to sum only the even numbers in a Range

Here’s a trick I invented to sum only the even numbers in a range.

Let’s take, for example, the range F3:F21, in which there are both odd and even numbers (see attached picture).

The formula is:

{=SUM(IF(MOD(F3:F21,2)=0,F3:F21,0))}

The formula, which is an array formula, consists of three functions:

MOD(F3:F21, 2)

The MOD function in Excel calculates the remainder of an arithmetic division. There are two arguments to this Function: the first is the number(s) we want to divide [the dividend], and the second is the divisor.

For example: MOD(1,2), i.e. dividing 1 by 2, will return a remainder of 1.

Our array formula checks each and every number in the range after division by 2, which means: it checks whether the dividend is even.

IF(MOD(F3:F21,2)=0,F3:F21,0))

The IF function “wraps” the MOD function and checks the condition (for each number in the array): if it is even, it takes the number itself, otherwise – it replaces it by a zero.

SUM(IF(MOD(F3:F21,2)=0,F3:F21,0))

Finally, the SUM function “wraps” the previous functions and sums the whole array: since the IF “changed” all original odd numbers into zero, then we get as a result the summation of the even numbers only.

Since this is an Array Formula: After typing it do not press ENTER, but CTRL+SHIFT+ENTER, instead. Excel then adds the curly braces to indicate that this is an array formula: a formula operating on an array/range of cells, as opposed to a regular formula which operates on a single cell.

BTW:
A slight modification in the formula will SUM all the odd numbers in that range:
Just change the =0 into =1 if you want to sum only the odd numbers:

SUM(IF(MOD(F3:F21,2)=1,F3:F21,0))