Example

Generic Formula

Note: This is an array formula. Do not type out the {} brackets. Hold Ctrl + Shift then press Enter while in Edit Mode to create an array formula.

Range – This is the range of values which you would like to sum all even numbers from.

What It Does

This formula will sum all the even numbers from a given range of values. An even number is defined as any number that has a remainder of 0 when divided by 2.

How It Works

The MOD function returns the remainder of a number on division by another number. For example MOD(7,2) will return 1 since the remainder of 7 divided by 2 is 1. We use the MOD function to test if a number is even. A number will be even if and only if the remainder on division by 2 equals 0. In other words, N is even if and only if MOD(N,2)=0.

MOD(Range,2)=0 will create an array of Boolean values where TRUE will mean the number is even and FALSE will mean the number is not even. We then multiply this Boolean array by the Range to get an array where each value is either an even value from the Range or a 0. This is because N*TRUE equals N and N*FALSE equals 0 for any number N.

In our example MOD({1.36;-2;2;9;10;7;1.33;8},2)=0 will result in the following Boolean array.

When we multiply this by Range we get {0;-2;2;0;10;0;0;8}. Then SUM({0;-2;2;0;10;0;0;8}) results in 18 which is the sum of all even numbers in our range of values.