Best Microsoft Excel Bloggers

Thursday, March 18, 2010

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.

No comments:

Post a Comment


Ms. Excel- Resident Excel Geek