SUMPRODUCT using Conditionals

As I mentioned the other day, SUMPRODUCT is very powerful. If you looked at the earlier blog, you saw its original or basic use -Multiplying corresponding values in columns and then summing them. If you skipped that blog, you may want to go back and take a look at it first.

With SUMPRODUCT, you can do much more. There is an interesting discussion that explains in great detail how SumProduct works in case you are interested. http://www.xldynamic.com/source/xld.SUMPRODUCT.html
It is so good, that I don't want to repeat it but will just walk through an example.

I can do two different tasks with SUMPRODUCT 
First- Using the example on the left,  I can use it to add up all the Items Sold if the Cost per Item is equal to 2.

=SUMPRODUCT(((B2:B7=2))*(C2:C7))

The resulting answer is 2875. Reminds you of SumIF doesn't it?

Now, for those of you still using Excel 2003, yes- the good news is that you can have multiple conditions.  However, there is a twist to the syntax so it is a bit different.

To add up all the Items Sold if the Cost per Item is equal to 2 or equal to 1 you would use the following formula.
=SUMPRODUCT((B2:B7=2)+(B2:B7=1),C2:C7)

Notice that I am not using an asterisk * but a comma. If you use an asterisk you will get an incorrect answer. So, be careful.

All the conditions are considered part of Array 1.

 

   

 

Second task - Multiplying Corresponding Columns with a Conditional and then Summing

Anyone noticing that all of a sudden SUMPRODUCT is just summing?
What happened to the Product part of the function?

If I wanted to multiply all the Costs and the Units Sold if the Items equaled 2, the equation would look like this:

=SUMPRODUCT((B2:B7=2)*(C2:C7)*(B2:B7))

Interesting eh? Notice that the asterisk is back and that everything is considered part of Array1.

There is a lot to this function so take a look and play around with it.

Two-condition formulas: SUMIFS vs. SUMPRODUCT.(Microsoft Excel 7.0): An article from: Strategic Finance
Cheers!