There. I said it. SumProduct. Some of my friends in the U.K. seminars asked why I NEVER talk about SumProduct. And there is a really simple, but really stupid reason for it. I missed that day in Excel class. SumProduct simply was not in my arsenal of tools, and so I find myself writing complex array formulas when a SumProduct would do the trick. In todays Episode 662, we take a look at how to convert yesterdays array formulas to SUMPRODUCT formulas.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Yesterday we were dealing with Jeff's question where he had multiple conditions and Jeff had written an array formula.
Basiclly would handle this =SUM and then IF and frankly this is how I write these and this is how I show it in my seminars and this is how I put it on the website.
You know but every once in a while someone in the audience will call me out on this and they say you know you could use SUMPRODUCT.
Well, I have to admit, I didn't go to school the day that they talked about SUMPRODUCT, so I just never ever started using it But it is true that all of these complex array formulas that I build, could basically be replaced with a SUMPRODUCT and it's not that hard to do, if you've already mastered this kind of a formula.
It's very simple to change it, basically what you're going to do is instead of the SUMIF, You're just going to type a single function SUMPRODUCT Open parentheses and then at the very last part of the IF, the comma where you say what to do if it's true, we're going to multiply so get rid of that common replace it with a multiplication sign.
Basically that's going to take all of our data.
It's going to take a look and see if D= "Varizon", if B = "XYZ" and then if both of those are true. We're going to get the corresponding value from Column F.
If either one of them is not true, it's going to evaluate to a 0 and basically we're going to get a 0 for that row SUMPRODUCT.
It does not require you remember "control+shift+enter", a little bit better there.
I know a lot of people were SUMPRODUCT fans, and I get it now I understand.
I want to thank all the folks at the UK seminar. Rich and John, Vander, Hayden, who said.
You know hey, this is really a better way to go.
Why don't you ever talk about it on the website.
And I really just had to admit that I had never used it before SUMPRODUT very cool way to go.
So now, let's talk about Jeff's question yesterday, where he had multiple conditions.
He had multiple ands and ors and certainly you can build that together in a SUMPRODUCT what I did here, because I want to see if column D was either "Verizon" or "Ford".
Build an extra level of parentheses with the plus sign in the middle.
So, we have D2 to D564 = Verizon in one set of parenthesis.
Ford in another set of parenthesis. Those joined together with a plus and the whole thing in parenthesis.
You could continue this logic building multiple ands. Remember the and sign is the & or Ors, that's the + sign and a SUMPRODUCT and build incredibly complicated conditional sums using SUMPRODUCT.
So either the array formula that we used yesterday or the SUMPRODUCT, both great ways to go to solve this problem. I want to thank Jeff for sending this in.
Hey, this is our last podcast of the year and I have a question for you.
If you've noticed over the course of most of the year the formulas that we were talking about were a little bit fuzzy in the podcast and recently, thanks to Mike from "call for help" I'm up in Toronto.
Mike pointed out a brand new product that we can use called mpeg streamcast.
We started using mpeg streamcast right before Christmas.
Want to ask you if things are better.
Are you able to see the formulas better now?
Hey want to thank you for stopping by. It's been a great year.
We'll see you next year. We'll be back on Wednesday, January 2nd with another podcast.
Yesterday we were dealing with Jeff's question where he had multiple conditions and Jeff had written an array formula.
Basiclly would handle this =SUM and then IF and frankly this is how I write these and this is how I show it in my seminars and this is how I put it on the website.
You know but every once in a while someone in the audience will call me out on this and they say you know you could use SUMPRODUCT.
Well, I have to admit, I didn't go to school the day that they talked about SUMPRODUCT, so I just never ever started using it But it is true that all of these complex array formulas that I build, could basically be replaced with a SUMPRODUCT and it's not that hard to do, if you've already mastered this kind of a formula.
It's very simple to change it, basically what you're going to do is instead of the SUMIF, You're just going to type a single function SUMPRODUCT Open parentheses and then at the very last part of the IF, the comma where you say what to do if it's true, we're going to multiply so get rid of that common replace it with a multiplication sign.
Basically that's going to take all of our data.
It's going to take a look and see if D= "Varizon", if B = "XYZ" and then if both of those are true. We're going to get the corresponding value from Column F.
If either one of them is not true, it's going to evaluate to a 0 and basically we're going to get a 0 for that row SUMPRODUCT.
It does not require you remember "control+shift+enter", a little bit better there.
I know a lot of people were SUMPRODUCT fans, and I get it now I understand.
I want to thank all the folks at the UK seminar. Rich and John, Vander, Hayden, who said.
You know hey, this is really a better way to go.
Why don't you ever talk about it on the website.
And I really just had to admit that I had never used it before SUMPRODUT very cool way to go.
So now, let's talk about Jeff's question yesterday, where he had multiple conditions.
He had multiple ands and ors and certainly you can build that together in a SUMPRODUCT what I did here, because I want to see if column D was either "Verizon" or "Ford".
Build an extra level of parentheses with the plus sign in the middle.
So, we have D2 to D564 = Verizon in one set of parenthesis.
Ford in another set of parenthesis. Those joined together with a plus and the whole thing in parenthesis.
You could continue this logic building multiple ands. Remember the and sign is the & or Ors, that's the + sign and a SUMPRODUCT and build incredibly complicated conditional sums using SUMPRODUCT.
So either the array formula that we used yesterday or the SUMPRODUCT, both great ways to go to solve this problem. I want to thank Jeff for sending this in.
Hey, this is our last podcast of the year and I have a question for you.
If you've noticed over the course of most of the year the formulas that we were talking about were a little bit fuzzy in the podcast and recently, thanks to Mike from "call for help" I'm up in Toronto.
Mike pointed out a brand new product that we can use called mpeg streamcast.
We started using mpeg streamcast right before Christmas.
Want to ask you if things are better.
Are you able to see the formulas better now?
Hey want to thank you for stopping by. It's been a great year.
We'll see you next year. We'll be back on Wednesday, January 2nd with another podcast.
