Sum all of the negative values from this range, but only if the row ID matches a particular value. Mike and Bill duel it out in Excel. Although Mike comes up with a single-cell formula and Bill needed many helper cells, when we used Charles Williams Fast Excel V3, the helper cell solution was faster.
Transcript of the video:
Bill Jelen: Hey, welcome back.
It’s time for another Dueling Excel Podcast.
I’m Bill Jelen for MrExcel.
I’ll be joined by Mike Girvin from Excel is Fun.
This is our Episode 160.
My title Mike Wins the Points, also known as Confusing Array Formula!
Oh my gosh, Mike this is crazy.
This question is sent from Sweden.
Joseph T wants to SUM an array of cells, N2:U235.
But he only wants the negative values from here and he only wants the values from a row if the criteria over here matches the number 12 in B15 Mike, you've gotta be kidding me. All right, so I am gonna just do this the long, hard way. so, =SUMIF, this range, <0, which will give me all of the negatives, right?
So -3, I’ll copy that down.
So just summing the negative numbers, -3, -4, -5, -9.
That’s working.
All right, then I’m going to multiply that by a Boolean constant that says, =SUMIF(N5:U5,“<0”)*(B5=$8$15), which is going to be true or false but when I multiply a True or False, the True becomes 1 and the False becomes zero so what we should have is we should get all the negatives only when the number matches.
Now, here, this is one is 0 but there is no negative so that’s not a problem.
And it looks like that one is working and then finally, =SUM(M5).
I know when we run this one through Charles William’s FastExcel because I have all of these intermediate formulas before I get to the total, it’s going to be so much slower to calculate.
But Mike, that’s what I have.
So, I’m sure you’re going to blow me away with some amazing formula.
Let’ see it.
Mike Girvin: Thank MrExcel.
Hey, SUM and this helper column, I love it.
Hey, helper columns sometimes are faster than array formulas.
Totally, the solution you did here is totally cool.
Now, I’m actually going to take straight from MrExcel, we had this whole data set here compared to the less than 0, and then this whole column over here needs to be compared to that 12.
I’ll just take those things and slap them into a single cell.
Now, we’re going to have to add these so I’m going to use =SUMPRODUCT instead of =SUM because the array are going to handle array calculations without using any special key stroke to enter the formula.
So, the first thing is I’m going to ask the question of this column.
Now, one thing about this set-up, this is a different dimension than this.
If they were the same dimensions, we could just use =SUMIFS, right?
Ctrl+Shift down arrow, Ctrl+Backspace to bring the =SUMPRODUCT(B5:B235=B15)?
Now, another thing about this comment, this is set-up exactly like this comment was written.
But I’m curious, when would you have the criteria right in the middle of a column full of criteria?
Not quite getting that.
F4 to lock it now.
We’re actually going to multiply this range here times this over here and they are different sizes and we want that = to calculate first so I’m going to put that in parenthesis.
Now, we’ll do times and our second calculation will be the first cell Ctrl+Shift right arrow, down arrow, Ctrl+Backspace.
Are any of you less than 0?
Close parenthesis.
Now right now, the reason we’re not using array 1, array 2 is because they’re different size dimensions, some product can’t handle different sized dimensions.
But if I enter this right here, it’ll give me the count.
So there are six values that are equal to 12 and less than 0.
If we highlight that whole range and F9, this is actually giving me a same sized array as this table over here when we multiply this times this range that’s taken the Trues and Falses and multiplying them across the entire row.
In fact, I can’t see the values if I pull down the formula bar.
Maybe at the top, there are some of their 1s.
That means True, I found the 12 and less than 0.
But we want to not count but add.
Ctrl+Z to undo that.
So, since that array right there is the same dimension as that, I’m going to put a comma, and now I can MULTIPLY those 1s and 0s times this whole table.
Ctrl+Shift down arrow, Ctrl+Backspace, Ctrl+Enter and there is the -27.
All right, throw it back to MrExcel.
Bill Jelen: All right, Mike, yeah, I knew you would have something good there.
So, this is a single-column of values but when we multiply it by a rectangular column of values, it pops out into a rectangular column and then just letting some product do its thing, very, very cool.
Charles Williams: Charles Williams here.
Now, let’s see how those solutions stack up using FastExcel.
Bill Jelen: With FastExcel, we have a matter of calculation options and temporarily turn this into manual.
Mike, let’s test yours first because I think it’s going to win so we do CalcRange and 1.225 Milliseconds.
We’ll test it a couple of times – oh, 0.645, and 2.541.
So, it looks like about an average there of 1.5 Milliseconds.
Coming back to my solution with all of these helper cells, I’m going to have to select this whole range here and CalcRange - 2.222.
CalcRange – 0.744.
And CalcRange 0.731.
They are about equivalent.
A little bit slower than the first time but then the second times, it is not that bad.
So, although I had to take up a lot more cells, it’s still managed to calculate in about the same speed.
That is crazy, isn’t it?
All right, hey, I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel Podcast from MrExcel and Excel is Fun.
It’s time for another Dueling Excel Podcast.
I’m Bill Jelen for MrExcel.
I’ll be joined by Mike Girvin from Excel is Fun.
This is our Episode 160.
My title Mike Wins the Points, also known as Confusing Array Formula!
Oh my gosh, Mike this is crazy.
This question is sent from Sweden.
Joseph T wants to SUM an array of cells, N2:U235.
But he only wants the negative values from here and he only wants the values from a row if the criteria over here matches the number 12 in B15 Mike, you've gotta be kidding me. All right, so I am gonna just do this the long, hard way. so, =SUMIF, this range, <0, which will give me all of the negatives, right?
So -3, I’ll copy that down.
So just summing the negative numbers, -3, -4, -5, -9.
That’s working.
All right, then I’m going to multiply that by a Boolean constant that says, =SUMIF(N5:U5,“<0”)*(B5=$8$15), which is going to be true or false but when I multiply a True or False, the True becomes 1 and the False becomes zero so what we should have is we should get all the negatives only when the number matches.
Now, here, this is one is 0 but there is no negative so that’s not a problem.
And it looks like that one is working and then finally, =SUM(M5).
I know when we run this one through Charles William’s FastExcel because I have all of these intermediate formulas before I get to the total, it’s going to be so much slower to calculate.
But Mike, that’s what I have.
So, I’m sure you’re going to blow me away with some amazing formula.
Let’ see it.
Mike Girvin: Thank MrExcel.
Hey, SUM and this helper column, I love it.
Hey, helper columns sometimes are faster than array formulas.
Totally, the solution you did here is totally cool.
Now, I’m actually going to take straight from MrExcel, we had this whole data set here compared to the less than 0, and then this whole column over here needs to be compared to that 12.
I’ll just take those things and slap them into a single cell.
Now, we’re going to have to add these so I’m going to use =SUMPRODUCT instead of =SUM because the array are going to handle array calculations without using any special key stroke to enter the formula.
So, the first thing is I’m going to ask the question of this column.
Now, one thing about this set-up, this is a different dimension than this.
If they were the same dimensions, we could just use =SUMIFS, right?
Ctrl+Shift down arrow, Ctrl+Backspace to bring the =SUMPRODUCT(B5:B235=B15)?
Now, another thing about this comment, this is set-up exactly like this comment was written.
But I’m curious, when would you have the criteria right in the middle of a column full of criteria?
Not quite getting that.
F4 to lock it now.
We’re actually going to multiply this range here times this over here and they are different sizes and we want that = to calculate first so I’m going to put that in parenthesis.
Now, we’ll do times and our second calculation will be the first cell Ctrl+Shift right arrow, down arrow, Ctrl+Backspace.
Are any of you less than 0?
Close parenthesis.
Now right now, the reason we’re not using array 1, array 2 is because they’re different size dimensions, some product can’t handle different sized dimensions.
But if I enter this right here, it’ll give me the count.
So there are six values that are equal to 12 and less than 0.
If we highlight that whole range and F9, this is actually giving me a same sized array as this table over here when we multiply this times this range that’s taken the Trues and Falses and multiplying them across the entire row.
In fact, I can’t see the values if I pull down the formula bar.
Maybe at the top, there are some of their 1s.
That means True, I found the 12 and less than 0.
But we want to not count but add.
Ctrl+Z to undo that.
So, since that array right there is the same dimension as that, I’m going to put a comma, and now I can MULTIPLY those 1s and 0s times this whole table.
Ctrl+Shift down arrow, Ctrl+Backspace, Ctrl+Enter and there is the -27.
All right, throw it back to MrExcel.
Bill Jelen: All right, Mike, yeah, I knew you would have something good there.
So, this is a single-column of values but when we multiply it by a rectangular column of values, it pops out into a rectangular column and then just letting some product do its thing, very, very cool.
Charles Williams: Charles Williams here.
Now, let’s see how those solutions stack up using FastExcel.
Bill Jelen: With FastExcel, we have a matter of calculation options and temporarily turn this into manual.
Mike, let’s test yours first because I think it’s going to win so we do CalcRange and 1.225 Milliseconds.
We’ll test it a couple of times – oh, 0.645, and 2.541.
So, it looks like about an average there of 1.5 Milliseconds.
Coming back to my solution with all of these helper cells, I’m going to have to select this whole range here and CalcRange - 2.222.
CalcRange – 0.744.
And CalcRange 0.731.
They are about equivalent.
A little bit slower than the first time but then the second times, it is not that bad.
So, although I had to take up a lot more cells, it’s still managed to calculate in about the same speed.
That is crazy, isn’t it?
All right, hey, I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel Podcast from MrExcel and Excel is Fun.
