Using SUMPRODUCT FUNCTIONUsing 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 FUNCTIONTable 1Example 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.
————————————————————————————————————————————————————
Table 2Example 2236 -> {=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.
————————————————————————————————————————————————————
Table 3Example 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 FunctionTable 4Example 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}.————————————————————————————————————————————————————Table 5Example 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.
————————————————————————————————————————————————————
Table 6Example 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.
