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 cost 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 sum different columns. Here what we have is a set of data from the HR department on cost 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. So 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 624, I want to see the sum of these three. 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 Ctrl 1, change this to number formatting with a thousand separator. I 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 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 helps us come up with the overall formula and then we can make parts of this dynamic. And the part of this to 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 SUMIFS formula. Now I have one criteria, so the SUMIF will work just as well here. But I've trained myself to always use SUMIFS 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 SUMIFS formula because then you just need to add a criteria to it. If you had used the SUMIF formula you will have to completely delete that formula and write a SUMIFS formula. Best is to just stick to the IFS versions. At this point I'm going to act like I'm only looking for 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. 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 that 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 SUMIFS argument. Now that's the 127,000. That looks right. Now what I want to happen with this formula is that my sum 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, right? 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 closed, bracket closed. Okay, 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 as 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, and now close bracket. And now I can move to the row argument. So now I need to decide how many rows do I want to move down within each area. I want to include again all rows. So 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 somewhere shorter, somewhere 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 match function, you just need to use another function that returns the area number if you want number one, two, or three. 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 data sets 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 by just clearing it. Now I turned 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, let's say joins the 809 cost center. Now check this out. This is 809, it's 12,300 for 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.