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.