A simple
trick 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:
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))
אין תגובות:
הוסף רשומת תגובה