Excel Magic Trick 777: SUMPRODUCT Function -- Basics To Advanced (14 Examples)

welcome to Excel magic trick number 777 hey if you want to download this workbook click on the link below the video in this video here Wow we have huge video I'm going to do all sorts of examples of how to use the sum-product function I'm going to start easy with the basics of then get a little bit more advanced now some product sorry I'm a little bit sick shooting this video but some product well they named the function smartly some the product okay let's see how to do this example longhand and then we'll see how to do use the some product to make a single cell calculation we have units and prices so what we usually do is we highlight some cells and the active cell we say equals two cells to my left times one cell to my left to populate all the phones as I hold ctrl and tap enter then I click on this cell and hit the keyboard shortcut for auto sum alt equals alright so we use two column did individual calculations and add it now notice what's happening here column column column with units column with prices the two first cells multiplied times each other enter f2 the second two cells in the two ranges enter f2 the third two cells in the array is another way to call another word to call the ranges so really we're multiplying two ranges and then adding so multiply multiply multiply multiply and then add so down here I did it multiply plus multiply plus multiply plus so really the way the sum product will work is it'll take two arrays first do the multiplication right get the answer and then add that's why it's called some product I sometimes think it should be called product sum because it it multiplies first and then add but really it means sum all of the products here it is equal sum product now notice the screen tip array comma array comma array so you can multiply many arrays and it does say array which means later when you get into the realm of array formulas this argument can handle arrays now this video isn't so much about a ray phone is just about the Sun product but both one cell we simply highlight the first same dimensioned array meaning one column four rows and then I hit a comma and then get the next one these are the same size or dimensions on these arrays and thus the sum product war and when I hit enter that is awesome one cell substitutes for an entire column and then some function now I'm on the sheet some product let's go to some product to here's another example we have the bills and here's how many we collected for the day we have five 100's 20 20 s 22 tens 5 5s and 38 10s so really we want to multiply each one of these and then add so the sum product simply does it for us array one horizontal same size dimension 1 row 1 2 3 4 5 6 columns and then a comma the Ray 2 and enter so there is a quick beautiful example when to use some product let's go back over to the sheet some product alright now our second example we some product because the nature of the calculation warrants it now this is from finance we usually have probability for we've estimated the economic state to be week normal or boom and so we've estimated some probabilities a massively subjective process but nevertheless people do it and then we have estimated the return for each stock in each state so here's what you really want to do and if you're learning a textbook you say oh the probability times the return we accept expect plus the probability times the return plus probability times return this is actually a type of weighted average type of calculation but that's how you make that calculation again you notice two same sized arrays or ranges multiplied and then add and so we can simply use some product in its place array 1 comma array 2 and there we go don't even have to put that last parenthesis enter alright another example and I'm going to have to make this a little bit smaller when to use some product well the dimensions of the arrays some product must be the same if they're not you can use a multiplication symbol so here's a little bit more complicated example from finance we have stock one stock two there's a probabilities here's our returns we've estimated for each state of the economy and here's the weight of our portfolio so watch this if you know how to use mixed cell references you can highlight this oh excuse me whole range right here in the active cell equals well the stock return and as we copy this formula down and over it'll move relatively times the column header which needs to be locked when we go down but not to the side so with my cursor next to C 22 I hit f4 key and lock the row reference but not the column times and then the row header which needs to be locked when you go this way but not when we go down that box when we copy this formula down that little dancing ants box needs to move so we need to only hit the f4 1 2 3 times lock the column but not the row I'm going to populate all of these cells using ctrl + Enter and then we add and so this would give us our i alt equals and then highlight this range that gives us the expected return now the problem for the sum-product is these are not the same arranges but would you believe it if you know how to use mixed cell references and you often have column headers and row headers you can just use the multiplication symbol so I'm going use some product highlight the first gray and it does not matter which order you do multiplication symbol so notice it's treating it as just a single array multiply multiply as long as use those multiplication symbols it will work I hit enter that is simply awesome now oh no these are different don't get trick by formatting now the same all right now the next example of some product is in earlier versions of Excel people would use some product to count or sum with more than one criteria now we don't so much have to do it anymore because we have some ifs and countifs and average ifs etc but there's lots of examples where what we learn here will come help us greatly because some product can handle arrays but there's some notes that I'll let you read that the first thing I want to look at them I'm going to delete this here's our situation we need to count or sum and we have two criteria first criteria for sales rep is give us sales rep 1 second criteria for product we need product 1 so if we said SR 1 P 1 we get probably this one and this one now to understand how to use the sum product we need to talk about boolean logic or common sense sometimes but it's really boolean logic now what happens when you give either and by the way let me just put this into edit mode here's the sum ifs this is what how you do it now you say the sum range well we want to add up all the sales and then criteria range one there it is the sales rep criteria one I highlighted that cell right there criteria two range criteria range to Bush there it is and then finally the criteria to a highlight of that now some ifs is you 100% want to use this if you're in this situation because there's and you're in Excel 2007 or 10 because the some ifs is much faster calculating more efficient at calculating and then the some product but in earlier versions we didn't have the S version of some if so we had to use some product so I want to show you the some product to solve this same problem because maybe you're going to work on an earlier computer but also because later we'll have to use some product cuz some ifs can't do thing but let's just think about this what did this do what it took that what the some ifs did is it went through here and it goes true match the criteria false false false true true and that went over and got this it kept going down false false true true so it went over and got this so really got the 12 and the 11 for the adding part of this and when I control enter it gets 23 so that basic common-sense idea of how to track criteria through two columns and by the way they don't have to be columns they can be turned on their side that could be row row but they do have to be the same dimensions that idea of going true true because there were two truths one two we're allowed to use this that is the secret to understanding how the sum product does some it's more fancy formulas and also boolean logic now look at this here's a little string here we got a true here but a false here so really true false this true right here in this array and then you look over here we had product one was our criteria so false false true it's only when you get a 1 times a 1 times the corresponding value in this value that you're allowed to count that's the boolean logic way of doing similarly when we get to this next one right 1 times 1 times 11 you're allowed to count it so 11 1 times 1 times 12 plus 1 times 1 times 11 is 23 now I want to explicitly do this because when we do our tricks in some product when we say is this cried does this criteria match here it won't come out one so we have to do an additional trick I want to just try it here I want to do equals and then this right here does that equal relative cell runs to this and then I'm going to hit f4 control enter then I'm going to drag it down now I'm going to come over here and I'm going to say equals this relative cell reference equals this and then f4 control-enter and copy it down you could see only where we get true true 12 should we be adding it here and true true 11 we should be adding here but our some product can't handle true so we have to coax a 1 and a 1 and a 0 out of true now there are lots of ways to do this you can multiply times 1 you can divide by 1 you can raise it to the first power but the fastest calculating way is to use a double negative the negative sign is a unitary operator you put two of them together and just because the people at Microsoft calculated it in the way they programmed it in the way it did it is the fastest way to convert trues and falses to ones and zeros I'm going to put a double negative now that won't work because it's a unitary operator it would only operate on this and we need to get this whole thing to to evaluate to true first so we have to put parentheses now if you know your order of operations or excels order of operations a comparative operator comes way after the negative side so that will do it this part right here if I highlight this and hit the f9 key you can see it's true and then I'm going to control Z if now I highlight this whole thing here and hit f9 you can see it does convert it to one control Z to undo that that f9 trick is good because you can see parts of a formula how they evaluate I'm going to ctrl enter and double click and send it down now if I'm fancy here notice I see this and this I want to copy this over here so I'm going to click here and hit f4 and lock it going down but not to the side that way this goes down and it's locked there but when I move it over here the criteria will move to there and this of course will move relatively throughout the whole action so control enter double click and send it down and drag it over here is how it works in boolean logic we say equals one times 0 times 16 that is how our boolean array multiplying with trues and falses and trues and falses converted to ones and zeros that's how it works so one times zero obviously is zero so as soon as you get any false anywhere when you're using an this is an criteria that means they both have to be true right zero anywhere if you have five criteria a zero anywhere negates this it's not going to be counted so this of course is going to be zero I drag it down we get the 12 and the 11 and then we add now this this is important for people that are learning this for the first first time to go through and see exactly how all this works now let's put it all together this is exciting whoops two count equals some product and the first array well we have to put a double negative and open parenthesis and notice it says array it can handle this watch this I'm going to highlight this whole column and equal sign and click on this now when we if you know how to use some ifs and count ifs the criteria has to be separated by a comma but not with some product you're actually doing calculations right directly on the range or array now the thing about this is is this is a normal logical formula logical formulas any comparative operator between a cell and a cell is what you normally do gives you a true or false but here because we did a whole range this makes it an array or array operation so when I highlight this it's not going to give me one true or one false it's going to give me an array now when I hit f9 here this is an array you can see we're using array syntax curly brackets always house the array semicolons means row and commas later we'll if I don't think we'll see it in this video but a comma means column now the way I always remember it is comma starts with a c and column starts with a c so that's how i memorize and then I just go oh yeah the other thing semicolon is the row now I'm going to quickly undo this control Z because you don't want to leave that hard-coded in I'm going to highlight this and hit actually I have to close parentheses highlight this and hit f9 and you can see sure enough just as we had over here just as we had here control Z now we do our second array that's array 1 comma to get to the next rate double negative open parenthesis I'm going to scroll over here equals criteria number 2 close parentheses close parentheses and that's it that's the sum that's if you're going to have to use some product using those double negatives and this construction here is efficient so there's two now I'm going to copy this and we want to do the same thing we do down here I copied it in edit mode escape click here f2 to put in edit mode ctrl V click right here now we have array 2 we want to Ray 3 comma so we simply type a comma get to make sure the screen tip says array 3 and then highlight very nice so that whole little discussion there is how boolean logic and a lot of the cooler formulas with some product works now I'm going to scroll over here's our next example okay so here sorry for sniffing I'm just sicker than anything this was to show you how people used to do it before some ifs and count ifs and average ifs now we're going to see an example that summit some ifs just can't do right sometimes you go to use some product because some ifs encounters can't handle arrays all right and here's the problem we have 2009 as our criteria and we have serial number dates these are number of days and we just need to match a part of it that 2009 now I'm going to show you the extra column method first and talk about the year function year is awesome serial number see your function smart it knows that under a date is a serial number number of days since December 31st 1899 so serial number I'm going to click right there it will just flat-out extract the year control-enter double-click and counter and drag it down now you could you know here do what we're counting some countifs and now the criteria range would be this and people do this all the time they add extra columns to their data set which is no problem however sometimes you you cannot for space and sometimes the data sets are too big and you don't want to add extra lots of extra calculations like this so actually sometimes people add extra columns to speed things up but that's not a topic in this class again if you don't want to add lots of extra columns like this we'll see how the sum-product can get around this so countifs can handle this because this is just a normal range a range of cells countif can handle but if we start doing those true/false double negative arrays inside account if it won't work you can't put years function inside of count ifs like you can the sum-product all right so there's comma criteria one it's going to be this comma criteria two I'm simply going to highlight this column here okay that's criteria range two comma and then the criteria is Julie so we get three now how do we do this without an extra column well the years function is the trick so we say equals year and I'm going to highlight this whole column control shift down arrow and have to close this off and just look at what this does I mean hit f9 there's our array so if you control Z put a in in the serial number argument for a year that argument is expecting one serial number as soon as you put a range in here this converts this to a non normal formula to an array formula and you can hit f9 and see it is returning multiple years it just looked through this whole column and did the 9s the 8 etc controls Z so how do we match that array inside of some product well you have to do a logical formula you have to say is it or any of those in that array equal to this and when you highlight this and hit f9 you can see sure enough there's our array of trues and falses control-z that is not going to work notice we have an equal sign we can't just come out here and go double negative because that double negative is attached to this this will calculate before that so if we want that to calculate first we have to say boom and then when we highlight this and hit f9 there is our lovely arrays of ones and zeros control-z now you can come over here and do some product there's array number one comma array number two double negative open parenthesis this column we're doing building arrays inside of some product so we have to just put the comparative operator directly on the range and then click on our criteria any comparative operator greater than greater than equal to go there to could go there also all right and then close that off and close that off and enter three now to add you copy the whole thing and you simply add our third that we have array two so I'm going to three I'm going to type a comma there's the array three and what do we want phone calls so there's our some product extracting in essence year criteria from serial dates in a single cell counting adding now let's up the ante here I wonder if I uh if I copied this here and pasted it down here I think this has the same dimension look at that so I just copied this cell right here not in edit mode I copy the actual cell because notice these are all relative cell references but the relationship between that cell calculating all this data right down here if I copy this cell it's the same as down here so when I put it into edit mode boom okay now we have to add one more criteria here actually I didn't want to do that it was just a silly I'm going to delete that here we have a completely different situation sorry we have fab and 2009 well there we saw how to use the Year function to get from serial numbers of the Year well there is a month function but it gives you month one two three four all the way to 12 and our criteria is given as f eb so we're going to have to use something different when you use something called the text function all right we're going to do the extra column method and then i'll show you how to do it with some product and this is pretty cool because we're going to see how to use the text function equals text but before we can do that let's talk about custom number formatting I'm going to highlight this range right here and control one to go to format cells number date now if we click on this right here notice what it says Friday January 2nd 2009 hmm that's that date right there that's not quite we want notice our criteria up here is given as fab ok no problem I'm going to come down to custom and I'm going to highlight this cell right here and just hit the Delete key let's just watch what happens to the sample here if I type month which is M there's one month okay so two M's would give us zero one and month no way the secret custom number format for three abbreviation of a month for three digits three characters is mmm now let's do Y and you can see an oh nine Y Y and three Y's will give us the year let's just click OK what that's pretty useless there but guess what it's not going to be useless when we use the text function and we use that custom number format because that means we can look at a serial number with the text function and show January 2009 in a single cell and copy down the column we will then be able to use that to match against two pieces so I'm going to control Z here just to leave those dates I'm going to come over here equals text excuse me tab the value text function works on any number and you can use any number though that's why it says value and not serial number comma and you can put any custom number format here I'm going to and it has to be in double quotes a double quote and I'm going to type mmm here year year in double quote close parenthesis now date custom number formatting is easy MS are months wise our years and days are that's right I heard someone out there say it DS control-enter double click and send it down oops won't double click now what I really want is trues and falses so I'm going to edit the cell at the top the actually we're not going to do that we're going to come over here and we can now use count F and let's see how to do that count or countifs countifs criteria range one well okay this one right here comma but what in the world is the criteria it's simply and in the right order February ampersand 2009 that is joining two criteria if I highlight this in hit the f9 Wow that is so cool it's exactly like all the criteria in this column over here control Z so now that's cool because we we took two criteria and just slapped it into one comma the criteria arranged to will be for Julie so I'm highlighting that criteria range two color criteria to is Julie alright and then control enter and we can see there's two now how do we do that all together we come up here and we have to go equals text of this whole column here same concept as the year right as soon as you slap more than one value into this argument for text you're in jump into the realm of arrays it means it's going to deliver lots of answers comma format text in double quotes mmm year year year and double quote close parenthesis now let's just highlight this and hit the f9 key ah that is so cool right in a single cell I can see lots of values controls you by the way if I enter this I get a value error the reason why is because Excel doesn't know how to display lots of values in one cell if you really wanted to enter it which is kind of meaningless here you'd control Shift + Enter that's a special keyboard shortcut that says I'm entering an array into a single cell it's kind of and if you look up here you can see the curly brackets that means you use control shift enter to enter this into the cell later we're not going to talk about a rave for MS here but that is how to get it to enter normally when I'm doing this just as I'm building a formula I just see an enter and I say Oh I don't care about the value error it's just saying you can't put lots of values into a single cell now what do we have to do we have to say is anything in that range equal to boom that ampersand this and when I highlight this by the way again you can go f9 and see sure enough that's working when you highlight this it'll just give us a bunch of trues and falses f9 ctrl Z and we need to force this equal sign to operate first so I'm going to put order of operation and then double negative in front of this now I want to highlight this that's the hard part of the the sum-product formula we're building so I'd like to build it first like this hit f9 and there it is that's what I'm looking for ctrl Z now I put it inside of the sum-product again this argument is awesome it says array and it's telling the truth it can handle arrays if you put this piece into or even this piece into the countifs it just doesn't it doesn't even give you a polite error message it just says I can't do that all right so there's our first arrays of ones and zeros to multiply by what comma we need to still get the julik the the sales up so I highlight that array equals to Julie oh yeah I forgot to put the parentheses and the double negative so I'll go double negative open parenthesis that's our second array comma finally array three we need to we're just counting here so we only need that one those two so notice here it goes 1 whenever it sees a 1 times 1 that's 1 which is a count of 1 I'm going to copy this and for summing escape click right here we'll come to the end array 2 I type a comma I get 2 array 3 so there's my comma and I simply if I want to add them I'll highlight those ctrl enter and there are 4 because there should be 2 matches right February 2009 Julie 2 Oh I can't see it in my delirious 600 there it is February Julie to Sochi yeah that's right right there okay those are two examples the last two examples are when to switch over to some products when some ifs won't work in you don't want to add an extra column now we do have one last a great example for Wendy example I'm going to click on the sheet' sp3 now this is a weighted average cost example now what is weighted average cost if you buy inventory one at 13 one at 15 one at twelve point five there are many ways to value inventory and calculate what cost a good soul should be on your income statement one way is weighted average cost you simply add up these and divide by three now there's three of them right each one didn't cost the same amount so 1350 would be our answer now data usually doesn't come simple like this it usually comes in datasets with field names at the top so for example we have a date that what we what product we bought what the quantity was and how much we paid each well the first problem here is how do we add up all of the cost for product 9 well what we need to do is go through this column and say ok product 9 product 9 product 9 true true true true true and then say this times this so just for this small example I'd say this times this Plus this times this that would give me the total I paid right because this is the quantity and this was the price in fact let's control shift down here arrow and control shift 4 to add some dollar signs to differentiate this is the quantity this is the price right so you get the idea of the problem here is we have to multiply only certain items in these two ranges ah perfect for the sum product because we can say is anything in this equal to anything in this column equal to 9 if it is then take the corresponding item from this array and multiply it by the corresponding an item in this array now technically we're going to have to do is we're going to multiply we're gonna have to say anything in this range equal to nine times with our array double negative at times this times this so let's just go over and see if we can add up all of the cost we paid for product nine and then we'll worry about dividing by how we're going to figure out how many we actually quantity wise let's just do the adding of the the total cost so equals some product and let's see I'm going to scroll over here the first part of this is going to be double negative open parenthesis and then this column control shift down arrow f4 anything in there that's a locked range equal to one cell to my left so that'll be product one and as we copy down obviously that will change that means the trues and falses will change as we copy this down alright so that's going to establish the true-false pattern then we simply for array two and three slap these two ranges in so I'm going to comma array two Oh quantity control shift down our f4 comma array three oh that's the price control shift down our f4 so again just visually here it's going to say true so we'll take this times this so it'll be one times this times this and that will give us on this one day what we pay for product total costs we play for product nine all right I'm going to just enter this see if we can get the total that's the total I'm going to double click and send it down come to the end click there and f2 make sure I got all my range is locked right they look correct now that's not how you do weighted average cost you also have to divide this total paid by the total number we purchased well we can do we don't need anything fancy here because we can do some if for some ifs and say find anything in this product that's product nine and then add up all the corresponding values in this range so I'm going to come over here and divide this by so now you can do some if or some ifs whichever one you want I was like some ifs because the screen tip is more polite so the some range well I'm adding up units control shift down our f4 comma the criteria range well I need to for this calculation when I hear any product one so I have to highlight this control shift down arrow f4 so criteria raised one is are all of the products and then comma the criteria will simply be one cell to my left control enter so that's a pretty fancy formula for weighted average cost of an average cost now the beauty of this and I can check my cell references is that I didn't have to add some extra columns right and there's an example if you download this workbook of the extra column method alright that's a lot about some product and how some product can really do some magic for us straight as a some product like back over here this one right here just multiplying two arranges right or over here we saw if you were in tooth versions of Excel adding so we did this formula counting with more than one criteria was hard so some product came to the rescue we also saw some product really can handle things that some ifs and countifs cannot and finally we saw this great weighted average cost calculation alright we'll see you next trick you

Channel: ExcelIsFun

Views: 151,343

Rating: 4.9238095 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, SUMPRODUCT, function, multiply, then, add, arrays, Sales, Currency, Portfolio, Expected, Return, Converting, TRUEs, and, FALSEs, to, ones, zeros, using, double, negative, Boolean, Logic, Math, Adding, with, criteria, for, 2003, Earlier, Summing, YEAR, Add, Count, two, three, TEXT, calculate, Weighted, Average, Cost

Id: vxpeEf8MYaY

Length: 36min 27sec (2187 seconds)

Published: Tue Apr 19 2011