User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

Using SUMPRODUCT FUNCTION
Using  double unary minus (--)  n SUMPRODUCT Function -> A significant use

------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Excel SUMPRODUCT Function:

The SUMPRODUCT function multiplies corresponding items of two or more arrays and then sums up these products.

Syntax: SUMPRODUCT(array1, array2, … array255)
 
1. array1, array2, ... array255 are arrays whose corresponding items are multiplied and then summed up. Each array is separated by a comma. Minimum 2 arrays are required, which can go upto a maximum of 255 in excel 2007 and excel 2010 (maximum limit was 30 arrays in excel 2003).
 
2. It is required that all arrays must have the same dimensions, else the function will return the #VALUE! error value.
 
3. Non-numeric items in an array are treated as zero.


Using SUMPRODUCT FUNCTION


Example 1

=SUMPRODUCT(A1:A4,B1:B4)  [Formula]
 
The formula returns the value 89, which is the sumproduct of 2 arrays. (Refer Table 1)
 
Multiplies all the components of the two arrays and then adds the products: A1*B1 + A2*B2 + A3*B3 =  3*9 + 4*7 + 5*2 + 6*4 = 89.
------------------------------------------------------------------------------------------------------------------------------------------------------------ 
 
Example 2

236  ->  {=SUM(A1:B4^2)}  [Formula]
 
The array formula returns the value 236, which is the sum of the squares of the elements of an array. (Refer Table 2)
 
Enter "=SUM(A1:B4^2) as array formula", it will look like {=SUM(A1:B4^2)}. To enter a formula as an array formula, type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
 
Formula calculates the sum of the squares of the elements in A1:B4: A1*A1 + A2*A2 + A3*A3 + A4*A4 + B1*B1 + B2*B2 + B3*B3 + B4*B4 = 9+16+25+36+81+49+4+16 = 236.
------------------------------------------------------------------------------------------------------------------------------------------------------------ 
 
Example 3

=SUMPRODUCT(A4:A5,B4:B5) [Formula]
 
The formula returns the value 24, which is the sumproduct of 2 arrays. (Refer Table 3)
 
Non-numeric entries are treated as zeros.
 
Formula result = A4*B4 + A5*B5 = 6*4 + 0*3 = 24.
 

Using double unary minus (--) in SUMPRODUCT Function


Example 1

=SUMPRODUCT(--(A1:A3>3),B1:B3)  [Formula]
 
The formula returns the value 9, and shows use of double unary minus (--) in sumproduct function. (Refer Table 4)
 
Formula returns the sum of B1:B3 wherein the corresponding row entries of Column A are greater than 3.
 
Formula result = 0*9 + 1*7 + 1*2 = 9.
 
=SUMPRODUCT(A1:A3>3)  [Formula Break]
 
This part of the formula evaluates to {False,True,True}.
 
A comparison returns a BOOLEAN value, which is non-numeric ie. TRUE/FALSE.
 
A non-numeric (BOOLEAN) value is coerced into a numeric value by Excel, by using arithmentic operations. TRUE will become 1 and FALSE will become 0 , =TRUE + 2 will give a result of 3, =FALSE + 2 will give a result of 2, =-(TRUE) will give a result of -1, and so on.
 
=SUMPRODUCT-(A1:A3>3)  [Formula Break]
 
This part of the formula evaluates to {0,-1,-1}. The first unary minus operator coerces the array to {0,-1,-1}.
 
=SUMPRODUCT--(A1:A3>3)  [Formula Break]
 
This part of the formula evaluates to {0,1,1}. The second unary minus operator negates the array to {0,1,1}.
------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Example 2

=SUMPRODUCT(--(A2:A8="John"),--(B2:B8="Jan" ),C2:C8)  [Formula]
 
Formula returns the value 64. (Refer Table 5). It evaluates corresponding Sales made by John in the month of Jan.
 ------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Example 3

=SUMPRODUCT(--(A2:A8="Tim"),--(B2:B8="June" ),C2:C8)  [Formula]
 
Formula returns the value 54. (Refer Table 6). It evaluates corresponding Sales made by Tim in the month of June.