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.
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.


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.
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:
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