Excel SUMPRODUCT with Criteria: SUM Alternate Columns based on Header and Criteria

In this video, I'm going to show you an alternate method to sum different columns based on their header and also based on a criteria. So in the last video, I showed you a version to do this using the SUMIFS and INDEXANDMATCH. And what we were trying to do was to find the sum of one of these three, so either bonus, benefits, or base salary, depending on what the user selected and what the user types in for the cost center. So in this case, for 809 bonus, we have 7,000 because it's the sum of these three. And if the user would change this to benefits, then they get this number. That would be this. There was a lot of great feedback on that video, and some of you shared alternate ways of doing this. Michael and Sanjay, they mentioned a way using the SUMPRODUCT formula, and Training Research mentioned a way using INDIRECT and Name Manager, both great ways of doing this. And in this video, I thought I'd share with you the SUMPRODUCT version because it's a very neat and clean formula. And while we're doing this, we can take a deeper look at the SUMPRODUCT engine because it really has such a flexible engine, and it can be used for a lot of complex situations. Another thing I wanted to do was to measure the speed of these two formulas and see if one of them is significantly faster than the other if we had a huge data set. I'm going to share with you my results towards the end of this lecture. First off, let's have a look at the SUMPRODUCT function. SUMPRODUCT basically multiplies and then sums. It's an array function. So if I highlight these two and then these two, what do I get? I get this times this plus this times this. Now I have a very detailed video on SUMPRODUCT. I'm going to put the link to it in the descriptions. If you're not familiar with this function, it's better you watch that one before you continue in this video here. What SUMPRODUCT is also great at doing is using criteria. So for example, if I want to check for 809 here, I can highlight this column and say equals this one. Now what do I get here? It shows a zero. So let's have a detailed look at this part of the formula. I'm going to press F9 and I get a bunch of true and false values. So it's checking this is this, it's a true. This is this, it's a true. Then it's false and the last one should be true here. It can't at this moment add up the true and false values. What we need to do is to turn the true values to ones and the false values to zeros. To do that, we just need to do any type of mathematical operation on this. So we can multiply it by one or we can use a double negative on it. But one thing you should remember is that anytime you use the equal sign there, always put that part of your formula in its own brackets. So I'm going to use double negation here. I get a three. What happens here? Let's press F9. It's turned the true and the false values to ones and zeros. So basically right now, it's actually doing what COUNTIFS normally does. Now what I'm going to do is to bring in benefits, but at this stage, I'm going to act like this is fixed. That benefits is fixed, that it's not shifting here, just to make sure that our SUMPRODUCT function works and then we can expand on it and make it dynamic. So I'm going to go to the next argument and bring these in. I get the same value. Here I use the Excel separator to do this because I'm already translating these two numbers and then it's multiplying it by these. But what I could also do, normally my preferred way of writing this is just to use the first section of the argument and multiply it by the second one. Then I don't need these anymore. Why? Because this multiplication sign is forcing the true and the false values to turn into ones and zeros. Okay, I'm going to press Enter. We can actually see it if we go to Evaluate Formula and now it's multiplying this by these and these are numbers here. So it's automatically turning the true and the false values to ones and zeros and multiplying them by these numbers. Okay, so remember the first two were one. That's why we get these two numbers and the last one was a one. So we get this one. By now we're doing what SUMIFS can do. How can we make a flexible SUMIFS out of this? Well, we need to bring in this part. Let me just take this part now away and add another criteria for the headers. Don't forget to open brackets again. I'm going to check, is this equal to this? Okay, close brackets. I still get my three because even though these are also true falses, these are true falses, it's multiplying a true by a true, so I'm getting a one. A false by a false, it's a zero. A true by a false, it's a zero. This part of it can be a bit confusing, so let's see this in slow motion. What I'm going to do is to highlight this part, press F9. Okay, this is the first part of the formula, which is basically checking if this equals to this. I'm going to Control-C this and press Escape to leave. Now I'm going to paste it right here, equals Control-V, and now not Enter, but Control-Shift-Enter. So basically we're just looking at this part of the formula here. That's what it returns, right? True, true, all false values, and then true. Now let's take a look at this part. I'm going to press F9, Control-C, press Escape to leave, and I'm going to paste it up here, just in the same way that these are. So highlight these three, equals Control-V, Control-Shift-Enter. Okay, so that's the result of the second one, is false, true, false, because I'm looking for benefits. Now let's multiply them together. This, I have to fix the column here. I'm going to press F4, F4, and F4 again, multiply by this one. What do I need to fix here? The one, right, the row here. So I'm going to copy this, copy down. You see the ones and the rest are zeros? So we're exactly at this stage of the formula, multiplying this by this. This is the matrix that we get out of it, and we can double-check by highlighting the inside part of the SUMPRODUCT function, press F9, and you see exactly that. It's just easier to see in Excel cells than here, because notice you get the commas. Commas means it's in the same row, and then you get the semicolon here, which means move to the next row. I'm going to press Escape to leave. Now we're at this stage. What do I have to do to get this result? I just have to multiply this by this one, and I get the answer. Why? Because what SUMPRODUCT does is this multiplied by this, this multiplied by this, and this multiplied by this, and then this multiplied by this, and so on, and then it adds them all up together. And because all of these are zeros and zeros, the only ones it ends up adding are the ones that are sitting right here. That's this flexible engine of the SUMPRODUCT function, and it can come in handy in so many different situations. So let's just double-check that they all are the same. That works perfectly well. Let's also change this to another cost center. Let's go to 805. That looks great, too. So that's how you can use the SUMPRODUCT function to get a flexible sum based on header and criteria. Now, which one of these do you think is faster? Well, let me show you my results. What I'm using here is the calculation timer VBA tool from Charles Williams. I got the link originally from Mike Girvin's CSE book. I'm going to share the link to the VBA code in the descriptions here. He has a great code where you can time different formulas and see which one is faster. I expanded the dataset to 30,000 lines, and I did three tests using the SUMIFS and the INDEX version, so that's this one, and then using the SUMPRODUCT formula. These were the results of the three tries. It did surprise me that the SUMIFS version was faster than the SUMPRODUCT version because I thought the SUMPRODUCT function is much neater, much cleaner, and much shorter, and it's just using one function instead of three different functions. I thought it would actually be faster. So it does pay off to test the timing of your formulas if you have really large datasets. On smaller datasets, it really doesn't matter which version you're going to use. Thanks again to Sanjay, Michael, and Training Research for sharing your methods with us. 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.