# Understand SUMPRODUCT Function and Start Using It Right Now

Learning to use SUMPRODUCT is a great investment, SP is 7 functions in one; it is the Excel Swiss Army Knife.

Just to repeat Excel Help: SUMPRODUCT “Multiplies corresponding components in the given arrays, and returns the sum of those products”.

Syntax: SUMPRODUCT(array1,array2,array3, …)

Where:

Array1, array2, array3, … are 2 to 255 arrays whose components you want to multiply and then add.

Using Excel language, SUMPRODUCT replace this formula: =A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8+A9*B9+A10*B10

With this other one: =SUMPRODUCT(A2:A10;B2:B10)

But, SP function can take the following form:

SUMPRODUCT((criteria array 1= criteria 1)*(criteria array 2= criteria 2) * sum array). You can replace (=) by the following operators: <. <=, >, >=, <>

What does this mean?

Here is where you must put more attention…

Imagine this formula: =SUMPRODUCT((A2:A8>=10000)*
(A2:A8<=50000)*(D2:D8=B\$14)*B2:B8)

Let’s name each argument…

• The criteria array 1: (A2:A8>=10000)
• The criteria array 2: (A2:A8<=50000)
• The criteria array 3: (D2:D8=B14)
• The sum array: B2:B8

Using words, the formula above would be:

=SUMPRODUCT(criteria column 1 * criteria column 2 * criteria column 3 * sum array)

Imagine what happens internally…

• TRUE * TRUE * TRUE * 100
• TRUE * FALSE * TRUE * 50
• FALSE * TRUE * FALSE * 10
• TRUE * TRUE * TRUE * 20

You would have

• 1*1*1*100=100
• 1*0*1*50=0
• 0*1*0*10=0
• 1*1*1*20=20

Now, if you sum the results, you will have 100 + 0 + 0 + 20 = 120

Well, this is what SUMPRODUCT does when you use it as a conditional sum formula. You can make conditional counts, Vlookup, etc.

See these examples:

• =SUMPRODUCT((EXACT(Haystack!\$B\$2:\$B\$7,A2))*Haystack!\$C\$2:\$C\$7)
• =SUMPRODUCT((MONTH(Haystack!\$B\$2:\$B\$7)=A2)*Haystack!\$C\$2:\$C\$7)
• =SUMPRODUCT((\$A\$2:\$A\$8>=DATEVALUE(“05/01/2009”))*
(\$A\$2:\$A\$8<=DATEVALUE(“05/31/2009”))*(\$D\$2:\$D\$8=B\$14)*\$B\$2:\$B\$8)
• =SUMPRODUCT((\$A\$2:\$A\$8>=10000)*
(\$A\$2:\$A\$8<=50000)*(\$D\$2:\$D\$8=B\$14)*\$B\$2:\$B\$8)

Now you have fully grasped SUMPRODUCT, go ahead and start using it.

You need SP and Array Formulas to take your reporting skills to the next level.