Mario sends in today's question. He has to do a SUMIF on a non-contiguous section of data. SUMIF can not handle this, but SUMPRODUCT can. Episode 886 shows you how.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Great question today by Mario.
Mario sends in this worksheet here, and he says, "I need to add up just the negative Deltas." And so he tried to do a SUMIF on non-contiguous ranges-- basically, calling out every column as part of the SUMIF, but SUMIF won't let you do that.
What we need is SUMIF with two conditions.
I want...
The first condition is, "Hey, is the number negative?" And the second condition is, Row 1 of this column, is it the word "Delta"?
If both of those conditions are true, then I want to add up the value; otherwise, I want 0.
Now, lots of different ways to do this.
In Excel 2007, you could use the brand-new SUMIFS-- plural-- to do it, but I'm going to do it the old-fashioned way here, using SUMPRODUCT.
Let's take a look at this formula that I've built over here.
SUMPRODUCT, basically we have three things that we're going to add or multiply together.
And the last item is our data in B2 to R2.
So that's one item, let's forget about that for a second.
First thing I have is a condition (B2:R2<0), and that's going to return an array of either true or false values.
So if the number is negative, I get a true; if it's positive, I get a false.
And then the next condition: "Look through Row 1.
So I put dollar signs before the 1 in both places and see if it's equal to the word "Delta".
And again this is going to return an array of basically false, true, false, true, false, true, false, true, to identify those true columns.
Alright.
And then what happens when we ask Excel to take these two arrays and multiply them together?
The first array of random trues and falses, and a second array of false, true, false, true, false, true.
When both of those are true, we get a 1; and when either one of them is false, we get a 0; so then I end up with an array that has 0s and 1s.
And basically, I multiply that then, by the numbers in B2 R2 and it gives me only the negative numbers.
Let me try and demonstrate for you here using Evaluate Formula.
When we evaluate the first array, you see we get a whole bunch of trues and falses and they don't seem to match any orders-- its wherever the number happens to be negative.
And then I'll evaluate the second array.
So now, here we have that predictable pattern of false, true, false, true, false, true, because it goes Number, Delta, Number, Delta, Number, Delta.
Now, here's where all of the magic happens when those two arrays get multiplied together: We end up with a beautiful array of 0s and 1s and the only place where we have a 1 is, like, here in the sixth column which is a Delta column-- which is negative-- and then over here another Delta column which is negative.
Most of the values evaluate to 0.
In the final step, when we multiply those 0s and 1s by all of the numbers in the columns, the only numbers that come through happen to be the negative Delta.
So we end up with that array, it sums it, and we get -17.
Alright.
So that's how I use SUMPRODUCT.
Now, I know a lot of people on the message board, they don't multiply; I multiply it because I spent a semester in logic design class where we did this all the time.
The folks on the message board at MrExcel they tend to use the minus minus.
Okay, what does minus minus do?
Well, minus minus takes this array and it coaxes the trues and falses to be 0s and 1s, and then they let SUMPRODUCT do its thing.
So here-- we'll take a look-- here we start out with B2 to R2.
When you evaluate, you get the trues and falses, and the trues and falses; but then because of the minus minus, you see that it actually converts the first array to 0s and 1s, and here it'll convert the second array to 0s and 1s.
And so now, basically, we're multiplying three things together-- it's either a 0 or 1 times a 0 or 1 times the actual number-- and we end up with the exact same result.
So whether you want to use the minus minus and separate each term with commas, or just my method of using the multiply which forces the trues and falses to change the zeros and ones, either one will work.
You could also build an array formula-- a Ctrl+Shift+Enter formula-- to do this.
And again, in Excel 2007, the new SUMIFS will handle this as well.
So lots of different ways to go.
Probably one of the most confusing formulas out there, but once you master it, you can start to do all kinds of very cool things like adding up only the negative Deltas.
Okay, I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Great question today by Mario.
Mario sends in this worksheet here, and he says, "I need to add up just the negative Deltas." And so he tried to do a SUMIF on non-contiguous ranges-- basically, calling out every column as part of the SUMIF, but SUMIF won't let you do that.
What we need is SUMIF with two conditions.
I want...
The first condition is, "Hey, is the number negative?" And the second condition is, Row 1 of this column, is it the word "Delta"?
If both of those conditions are true, then I want to add up the value; otherwise, I want 0.
Now, lots of different ways to do this.
In Excel 2007, you could use the brand-new SUMIFS-- plural-- to do it, but I'm going to do it the old-fashioned way here, using SUMPRODUCT.
Let's take a look at this formula that I've built over here.
SUMPRODUCT, basically we have three things that we're going to add or multiply together.
And the last item is our data in B2 to R2.
So that's one item, let's forget about that for a second.
First thing I have is a condition (B2:R2<0), and that's going to return an array of either true or false values.
So if the number is negative, I get a true; if it's positive, I get a false.
And then the next condition: "Look through Row 1.
So I put dollar signs before the 1 in both places and see if it's equal to the word "Delta".
And again this is going to return an array of basically false, true, false, true, false, true, false, true, to identify those true columns.
Alright.
And then what happens when we ask Excel to take these two arrays and multiply them together?
The first array of random trues and falses, and a second array of false, true, false, true, false, true.
When both of those are true, we get a 1; and when either one of them is false, we get a 0; so then I end up with an array that has 0s and 1s.
And basically, I multiply that then, by the numbers in B2 R2 and it gives me only the negative numbers.
Let me try and demonstrate for you here using Evaluate Formula.
When we evaluate the first array, you see we get a whole bunch of trues and falses and they don't seem to match any orders-- its wherever the number happens to be negative.
And then I'll evaluate the second array.
So now, here we have that predictable pattern of false, true, false, true, false, true, because it goes Number, Delta, Number, Delta, Number, Delta.
Now, here's where all of the magic happens when those two arrays get multiplied together: We end up with a beautiful array of 0s and 1s and the only place where we have a 1 is, like, here in the sixth column which is a Delta column-- which is negative-- and then over here another Delta column which is negative.
Most of the values evaluate to 0.
In the final step, when we multiply those 0s and 1s by all of the numbers in the columns, the only numbers that come through happen to be the negative Delta.
So we end up with that array, it sums it, and we get -17.
Alright.
So that's how I use SUMPRODUCT.
Now, I know a lot of people on the message board, they don't multiply; I multiply it because I spent a semester in logic design class where we did this all the time.
The folks on the message board at MrExcel they tend to use the minus minus.
Okay, what does minus minus do?
Well, minus minus takes this array and it coaxes the trues and falses to be 0s and 1s, and then they let SUMPRODUCT do its thing.
So here-- we'll take a look-- here we start out with B2 to R2.
When you evaluate, you get the trues and falses, and the trues and falses; but then because of the minus minus, you see that it actually converts the first array to 0s and 1s, and here it'll convert the second array to 0s and 1s.
And so now, basically, we're multiplying three things together-- it's either a 0 or 1 times a 0 or 1 times the actual number-- and we end up with the exact same result.
So whether you want to use the minus minus and separate each term with commas, or just my method of using the multiply which forces the trues and falses to change the zeros and ones, either one will work.
You could also build an array formula-- a Ctrl+Shift+Enter formula-- to do this.
And again, in Excel 2007, the new SUMIFS will handle this as well.
So lots of different ways to go.
Probably one of the most confusing formulas out there, but once you master it, you can start to do all kinds of very cool things like adding up only the negative Deltas.
Okay, I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
