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.