Excel SUMIFS: Sum Alternate Columns based on Criteria and Header

in this lecture I'm going to show you how to create a dynamic sum that's based on user selection so what we have here is HR data we have a list of cost centers and various HR related information by cross center now we want to create a dashboard type of report where the user can select the parameter they want to see and the formula is going to sum up the values for that cost Center based on the parameter which means that we need to create a sum range that's going to jump columns depending on user selection so let's see how we can sum not just by criteria but also some different columns here what we have is a set of data from the HR department on Khan Center name some additional information salary benefits and bonus and what we want to do is to create a report where we can input the cost center we can select from a drop-down list if we want to see the summed salary benefits or bonus for here I'm going to include a data validation select list and my source is going to be these three and here I want to see the sum of in this case base salary and if I had input 809 I just want to see these two so the sum of these two okay and if I input 6 to 4 I want to see the sum of these 3 and if I change base salary to bonus then I want to see the sum of bonus so let's just make this look more like an input field just going to add border here okay so let's take the formatting of this I'm going to double click this because I want to copy the formatting here and here and then I'm just going to go control one change this to number formatting with a thousand separator they don't want to have decimal places I'm just going to update the fill to have a very light gray fill and this is where I'm going to write the formula so now if someone inputs 805 I want to see the sum of these three which formula can I use here let's start simple and let's just act like this is static that also come up with the overall formula and then we can make parts of this dynamic and the part of this to make dynamic is going to be the sum range the formula that works well here because I have a criteria and I am summing is the sum efest formula now I have one criteria so the sum if will work just as well here but I'm chained myself to always use some offense because requirements always change and soon you might have to add a second criteria to check for example the difference between salaries in male and female or bonus between male and female and so on so it's easier if you have the summerfest formula because then you just need to add a criteria to it if you had used the sum if formula you will have to completely delete that formula and write a semi-fast formula best is to just stick to the if s versions at this point I'm going to act like I'm only looking for a base salary that it's not dynamic so I'm going to highlight this area the next one is the criteria range one that's the range where the criteria is sitting where's my criteria it's this one and where is the range where this is sitting the range is this one okay so I can find this number this cost Center number here so that's the one I have to highlight then where is the criteria itself but I'm looking up it's this one okay that's it for my criterias in this respect right because this is not a criteria that's here it's actually here I can't control it as a criteria using the normal some offense arguments now that's the 127,000 that looks right now what I want to happen with this formula is that my son range this one jumps to here and then jumps to here when this is changed to benefits and bonus so which part of this formula do I have to update the sum range raised and here the index function works quite well index and match is common to use when you look up certain values or certain cells in a large data table but index works just as well to pick out ranges from a large data table that you can use to feed other formulas that can handle ranges the first argument for index is the area where my answer is in so where my range is and my range can be anywhere here because it could be this it could be this or it could be this so I have to highlight all three the next argument is how many rows do I want to move down well I want to include all rows so I can skip this argument or I can put a zero here the next one is how many columns do I want to move and that now depends on this selection so here I can use a match and what do I want to look up this where do I want to look it up here and I want to have an exact match bracket close bracket close you can notice that my match area and my index area are in sync okay so now let's test that should be this one and for bonus 4000 that looks good let me change to 809 that changes to these two okay so that's how you can use the index function to create dynamic ranges that you can feed to other formulas that can handle ranges now just for fun let's try to use the second argument for index because you see when you type in the index function you get this one and you get this one the second one has area numbers which sounds like it can work well here too when you work with area numbers what you have to keep in mind is that you're highlighting different areas always always open your brackets again to keep the different areas in the same reference argument otherwise Excel is going to switch the next argument so let me show you if I would highlight this as my first area and then I want to highlight this is my second area notice how it jumps to the row argument but if I include this in its own bracket it stays in this argument okay and then again closed brackets and now I can move on to the row argument so now I need to decide how many rows do I want to move down within each area I want include again all rows I can skip this argument and now how many columns do I want to move within each single area each single area has just one column and I don't want to move any columns so I'm going to skip that argument as well now the last one is which area do I want and it works like this this is one this is two this is three here in this case I can use the match function again and look for this in here and look for an exact match that works just as well in this case the other version was easier to write but this version could make sense if your areas have different lengths so this is someone shorter some more longer or your areas are in completely different places and you don't want to highlight the whole area the only thing you need to think about changing is instead of using the merge function you just need to use another function that returns the area number if you want number 1 2 or 3 so these are two different ways of writing this now what happens when you add new data to this table well at this moment it's not going to update so whenever you have datasets that are going to expand always think about turning that data table to an official excel table and to do that you just click anywhere in the table and then press ctrl T table has headers yes you can remove this table formatting to go back to your own formatting which is clearing it now I turn this data set to a table after I had written my formula so it's not referencing the table headers but that doesn't matter because the moment I add a new person say joins the 809 Cross Center let's check the side this is eight or nine it's twelve thousand three hundred four benefits it immediately updated it and it immediately took it into account and it even expanded the ranges here so it doesn't matter if you do it before or you do it afterwards it will still include the newly added data lines to your calculations if you have other ways of doing this please do share in the comments below if you enjoyed this video don't forget to give it a thumbs up and if you like this type of videos don't forget to subscribe to this channel to get notifications when new videos like this one come out

Channel: Leila Gharani

Views: 286,873

Rating: 4.9318519 out of 5

Keywords: excel sum alternate columns, conditional sum, sum columns if, sum columns based on criteria, how to use sumifs, index area number, Index Match, sum column based on header, sumifs, sum different columns, Advanced Excel, Master Excel, Learn Excel Formulas, Excel formulas, Excel Tips, Excel Tips and Tricks, Xelplus, Best Excel Online Course, XelplusVis, Excel Advanced formulas, Improve Excel skills, Excel for analysts, Excel for controllers, Microsoft Excel expert, Leila Gharani

Id: 2odvwY_mWN8

Length: 10min 53sec (653 seconds)

Published: Fri Apr 21 2017