יום שישי, 23 בפברואר 2018

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



אין תגובות:

הוסף רשומת תגובה