יום שלישי, 13 בפברואר 2018

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))




תגובה 1:

  1. I prefer the shorter version:

    {=SUM((MOD(J3:J21,2)=0)*J3:J21)}

    ----------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” Excel MVP – Excel (2009-2018)
    ISRAEL

    השבמחק