Continuing yesterday's discussion about minus minus, we'll take a look at using SUMPRODUCT vs the new Excel 2007 SUMIFS to solve the multiple conditional sum problem. Episode 900 shows you how.
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast, Episode number 900-- our 800th daily podcast.
Yesterday, we talked about Kim's question and I broke down why we use minus minus in our SUMPRODUCT, but I never actually put it all back together.
So in case you've never seen this, I want to show you that you don't actually have to enter these twenty cells, and then the twenty-first cell; we can put it all in one big formula.
We use =SUMPRODUCT and then we're going to do minus minus and an open parenthesis-- did you see that?
Excel 2007 changed my minus minus to a dash; that is a horrible thing for Microsoft to do-- it's like they've never seen this trick we'll have to go back and fix that after we're done with the formula.
And we are going to choose everything from A2 to A11-- press F4-- is equal to the word East-- I'll press F4 1, 2, 3 times to freeze it to the column-- and then a comma, and now another minus minus, open parenthesis-- which they nicely change back to a dash.
And we'll go over here and look at everything in ABC, I'll press F4, see if it's equal to the word ABC up here, we'll press F4 twice to freeze it to just the row; and then finally, the last array is the Sales in C2 to C11; we'll press F4 there and 2 closing parenthesis.
(=SUMPRODUCT--($A$2:$A$11-12)--($B$2:$B$11-J$1),($C$2:$C$11))) Now, let's see if we can get rid of these dashes here.
If we do minus minus-- they seem to allow us to do that on editing.
Strange.
And I press Enter, I get 6,500.
And the great thing now, is I could copy this through.
I'm getting the total for each intersection-- for East ABC, DEF, XYZ, and it works straight through.
So you can put all this together in one big formula with a single minus minus.
Now, it's also possible, as I mentioned yesterday, to get rid of the minuses and just multiply everything so we're not actually putting separate elements of an array; we're just building the boolean logic right here.
So I'll get rid of the commas and the minuses and just put in the multiplication symbol and that works as well.
Or, if you're in Excel 2007, you can just get rid of all of this horrible logic and go back to the good...
Or the...
Start to use the brand-new SUMIFS-- the plural SUMIFS-- and that basically says, "Hey, we're going to look through this Sum range and then we specify pairs of conditions." Pairs of conditions.
So the first condition: Look through A2 to A11 and see if it's equal to East.
And the next condition, look through B2 to B11, see if it's equal to ABC.
And that's a slightly easier way to go in Excel 2007.
Interesting there, seeing that Excel's changing the minus minus to a dash-- very annoying feature for those who like to use minus minus.
They make you jump through a lot of hoops.
Just one of those things we'll chalk up to.
Go figure.
Alright, hey, thanks for stopping by, I'll see you next time for another netcast from MrExcel.
Yesterday, we talked about Kim's question and I broke down why we use minus minus in our SUMPRODUCT, but I never actually put it all back together.
So in case you've never seen this, I want to show you that you don't actually have to enter these twenty cells, and then the twenty-first cell; we can put it all in one big formula.
We use =SUMPRODUCT and then we're going to do minus minus and an open parenthesis-- did you see that?
Excel 2007 changed my minus minus to a dash; that is a horrible thing for Microsoft to do-- it's like they've never seen this trick we'll have to go back and fix that after we're done with the formula.
And we are going to choose everything from A2 to A11-- press F4-- is equal to the word East-- I'll press F4 1, 2, 3 times to freeze it to the column-- and then a comma, and now another minus minus, open parenthesis-- which they nicely change back to a dash.
And we'll go over here and look at everything in ABC, I'll press F4, see if it's equal to the word ABC up here, we'll press F4 twice to freeze it to just the row; and then finally, the last array is the Sales in C2 to C11; we'll press F4 there and 2 closing parenthesis.
(=SUMPRODUCT--($A$2:$A$11-12)--($B$2:$B$11-J$1),($C$2:$C$11))) Now, let's see if we can get rid of these dashes here.
If we do minus minus-- they seem to allow us to do that on editing.
Strange.
And I press Enter, I get 6,500.
And the great thing now, is I could copy this through.
I'm getting the total for each intersection-- for East ABC, DEF, XYZ, and it works straight through.
So you can put all this together in one big formula with a single minus minus.
Now, it's also possible, as I mentioned yesterday, to get rid of the minuses and just multiply everything so we're not actually putting separate elements of an array; we're just building the boolean logic right here.
So I'll get rid of the commas and the minuses and just put in the multiplication symbol and that works as well.
Or, if you're in Excel 2007, you can just get rid of all of this horrible logic and go back to the good...
Or the...
Start to use the brand-new SUMIFS-- the plural SUMIFS-- and that basically says, "Hey, we're going to look through this Sum range and then we specify pairs of conditions." Pairs of conditions.
So the first condition: Look through A2 to A11 and see if it's equal to East.
And the next condition, look through B2 to B11, see if it's equal to ABC.
And that's a slightly easier way to go in Excel 2007.
Interesting there, seeing that Excel's changing the minus minus to a dash-- very annoying feature for those who like to use minus minus.
They make you jump through a lot of hoops.
Just one of those things we'll chalk up to.
Go figure.
Alright, hey, thanks for stopping by, I'll see you next time for another netcast from MrExcel.
