No text or picture Add-ons were added yet. How sad!
Welcome back to Mr. Excel net cast episode number 900 or 800 daily podcast. Yesterday we talked about Kim’s question and I broke down why we use minus in our sum product but I never actually put it all back together. So in case you’ve never seen this, I want to show that you don’t actually have to enter these 20 cells and then the 21st cell we can put it all in one big formula and we use equal some product and then we’re going to do minus-minus and the open parenthesis. Do you see that Excel 2007 change my minus-minus to a dash that is a horrible thing from Microsoft to do it. It’s like they’ve never seen this trick. I have to go back and fix after we were done with the formula and we are going to choose everything from A2-A11 press F4 is equal to the word East now press F4 one two three times to freeze it to the column and then a comma and now another minus-minus. Open parenthesis which is they nicely change back to a dash and we go over here and look it everything in ABC I’ll press F4 see if that’s equal to the word ABC up here we’ll press F4 twice to freeze it to just a row. And then finally the last array is the sales and C2 to C11 we’ll press F4 there and two closes and parenthesis.
Now let’s see if we can get rid of these dashes here if we do minus-minus and they’re seem to allow to do that on editing strange and I press Enter. I got 6500 and the great thing now is like to copy this through—I’m getting the total for each intersection East ABCDEFXYZ and it works straight through so you can put it all of this together in one big formula with a single minus-minus.
Now its also possible because I’m just illustrate to get rid of the minuses and just multiply everything so were not actually putting separate elements in the ray. We’re just building the blue and logic right here so I’ll get rid of the commas and then 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 this horrible logic and go back to the good or the—start to use the brand new SUMIFS and that basically says hey we’re not let through this sum range and then we specify 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 the B11. See if it’s equal to ABC and that’s a slightly easier way to go in Excel 2007. Everything there seems that Excel is change in the minus-minus to a dash. Very annoying features for those who like to use minus-minus they make a jump through a lot of hoops just to one of the things will 0325 go figure.
Hey, thanks for stopping by, I’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services