Jeff from MA writes with todays question; creating a conditional sum array formula that uses OR functions instead of AND functions. Episode 661 shows you how.
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.
Today we have a question sent in by Jeff from Burlington, Massachusetts.
Jeff is trying to do some fairly complicated conditional summing.
Now you know SUMIF and COUNTIF back in excel 2003 and before could only handle one condition.
So if you got to be on two conditions, then we either went to an array formula or to a SUMPRODUCT.
Jeff is using the SUMPRODUCT version.
So let's take a quick look at this formula. Big long formula Here it's probably not even going to fit on the podcast screen Basically Jeff says. Hey, we're going to look at everything in Column D, and see if it's equal to Verizon and then multiply that whole array by everything in column B to see if it's equal to product "XYZ" and then if it is, use the corresponding value from F2 to F564.
What we get when we hit Ctrl+shift+enter is we get the conditional SUM 165727 and we can do a quick little test here if I would change one of these records to Verizon and we should see that that result of that formula changes. Let me undo okay now.
Jeff says well this is fine, but now I need to add some ors in.
I need to see if it's one particular customer or another customer or one particular product or another product.
Well, when you take a look at this formula, the multiplication sign right here, this multiplication sign between the, the two conditions is basically like an and and saying that both of those have to be true.
The equivalent operator for an or is a plus sign.
So if you would use a plus sign, say have one value or a plus sign another condition.
So, let's say that you would use one condition then a plus sign meaning or and another condition , you could actually build very complex conditional sums and that basically do both ands and ors so I rewrote the formula here, basically says.
Hey, we're going to take everything D2 to D564 = "Verizon" that's in parentheses + in other words or if D2 to D564 = "Ford" then go ahead and use F2 to F564.
Now Jeff you could make this as complicated as you want with multiple levels of parentheses.
I know that you also wanted to use another value, and so here we could then add another multiplier.
That would say *(B2:B564="XYZ") Control+shift+enter and you can combine as many different ands and ors as you want.
Just be careful that you use the right parenthesis.
Want to thank Jeff for sending that question in.
Now tomorrow, we're going to take a look at the exact same formulas instead of using the array formula, using SUMPRODUCTS. So be sure to stop back for that.
Thanks for stopping by. Will see you tomorrow for another netcast from MrExcel.
Today we have a question sent in by Jeff from Burlington, Massachusetts.
Jeff is trying to do some fairly complicated conditional summing.
Now you know SUMIF and COUNTIF back in excel 2003 and before could only handle one condition.
So if you got to be on two conditions, then we either went to an array formula or to a SUMPRODUCT.
Jeff is using the SUMPRODUCT version.
So let's take a quick look at this formula. Big long formula Here it's probably not even going to fit on the podcast screen Basically Jeff says. Hey, we're going to look at everything in Column D, and see if it's equal to Verizon and then multiply that whole array by everything in column B to see if it's equal to product "XYZ" and then if it is, use the corresponding value from F2 to F564.
What we get when we hit Ctrl+shift+enter is we get the conditional SUM 165727 and we can do a quick little test here if I would change one of these records to Verizon and we should see that that result of that formula changes. Let me undo okay now.
Jeff says well this is fine, but now I need to add some ors in.
I need to see if it's one particular customer or another customer or one particular product or another product.
Well, when you take a look at this formula, the multiplication sign right here, this multiplication sign between the, the two conditions is basically like an and and saying that both of those have to be true.
The equivalent operator for an or is a plus sign.
So if you would use a plus sign, say have one value or a plus sign another condition.
So, let's say that you would use one condition then a plus sign meaning or and another condition , you could actually build very complex conditional sums and that basically do both ands and ors so I rewrote the formula here, basically says.
Hey, we're going to take everything D2 to D564 = "Verizon" that's in parentheses + in other words or if D2 to D564 = "Ford" then go ahead and use F2 to F564.
Now Jeff you could make this as complicated as you want with multiple levels of parentheses.
I know that you also wanted to use another value, and so here we could then add another multiplier.
That would say *(B2:B564="XYZ") Control+shift+enter and you can combine as many different ands and ors as you want.
Just be careful that you use the right parenthesis.
Want to thank Jeff for sending that question in.
Now tomorrow, we're going to take a look at the exact same formulas instead of using the array formula, using SUMPRODUCTS. So be sure to stop back for that.
Thanks for stopping by. Will see you tomorrow for another netcast from MrExcel.
