Today, Mike and Bill attempt to sum the top 2 records that match a set of criteria. Advanced Filter? Array Formula? Which will they use?
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun, this is our episode 133 - Sum the Top 2 that match Criteria!
Alright, question from Joe JG from YouTube: Wants to be able to SUM the top 2 values from this range, but only the ones that match this criteria.
Alright, so I'm going to go old school here, select the range, Data, Filter, Advanced Filter, we're going to Copy to another location.
The criteria range are these 6 cells right here, and we're going to copy it to there, click OK.
Alright, so now that extracted range are only the matching records, and then what is it, it's =MAX!
This will select a whole bunch of cells here, because you don't know how many records we're going to get.
Plus the second largest value is LARGE of that same range, ,2 . There we go, 95 which is the required answer.
Of course, this method works great right now, but if any of the criteria change, we got to go back and start all over again.
But Mike has something better that, Mike, let's see what you have!
Mike: Thanks MrExcel!
Hey, I got to tell you, this kind of problem here, you know, as I see it, has a bunch of array calculations, it's going to get pretty complicated.
This is totally awesome, Advanced Filter based on that criteria, and then MAX + LARGE too, got to love it, especially if this data set is just a static data set, it's not going to change, and that 2 is not going to change either.
Alright, so the LARGE function, why don't we take a look at that, it needs an array, but if I highlight this column right here, I really don't want all the numbers, I want just the ones that match these three criteria.
So I’m going to build an array calculation here.
Now I'll start with the IF, see if anything in here is equal to that month there, comma, now I need the value of TRUE, but there's still more conditions.
So you put another IF, nesting it, it's anything in this column equals to that, comma, there's still another condition, so value of TRUE.
We have to put a third IF, anything in there equal to this year, comma, and now we can put the value if TRUE.
These are three array calculations, all in all they will deliver TRUEs and FALSEs, only where there's a TRUE, a TRUE, and a TRUE, will it pick out from this column of numbers.
Now, the IF function, if you have an array calculation and the logical test, it doesn't matter where the rest of the formula is sitting, or what other function is on the outside of the IF, it WILL require Ctrl+Shift+Enter.
Alright, so I'm going to close this off, 1-2-3.
So there, I'm back to the LARGE, so I type a comma, and then- oh wait a second, I don't want 2, because that would just give me the second largest.
I'm going to do another array calculation using an array constant, {1,2} ! That says "Hey LARGE, give me one AND two largest!" And then I'm going to close off the LARGE.
Now if I highlight this, we'll see that the LARGE will deliver two values, i'll hit F9, BOOM, there it is, Ctrl+Z.
Now I can put it, you know what, what would happen if I put it in SUMPRODUCT?
Forget it, I would still have to use Ctrl+Shift+Enter, watch this!
Remember, the trump rule for the IF is, any array calculation and logical test, forget it, doesn't matter what functions on the outside is, when I hit Enter, it's not going to work, if I do Ctrl+Shift+Enter, we get our 95, but here's the deal!
I had to turn off Speak on Enter.
Let's be unambiguous here, in case someone else is looking at it, and they're thinking "I know SUMPRODUCT doesn't require Ctrl+Shift+Enter", let's not give them the chance to think that.
Let's put SUM, then we're looking at this, we know all we need to do Ctrl+Shift+Enter, when you do Ctrl+Shift+Enter, you're saying "Hey Excel, I'm doing an array formula", you can see up in the formula bar, those curly bracket, that's Excel telling you "Yes, I understood that that's an array operation." Now we could amend this, because Copy, Esc, what if we didn't?
Or we wanted this to be variable 2?
Well we'd have to get a little tricky here.
Now let me click Ctrl+Shift+Enter and just come off to the side.
I'm going to use the INDIRECT function, and I'm going to build that array constant, but it will be dynamic.
In double quotes I'm going to say "1:" . So that colon tells us we're going from row number 1 to some other row.
I'll use & to join and, for right now, it's from one to two.
Now INDIRECT this program to take a reference, that's text, if I F9, you can see it's text there, and convert it to an actual reference.
When I highlight this in F9, and won't let me because row 1 to 2 has too many references in it.
But no problem, we can tell the ROW function to look at that and tell me how many rows, well there's only two, 1 and 2.
So highlight this in F9, there it is.
Now I'm going to Ctrl+Shift+Enter, and change this to 3.
And now, if we come down here and highlight it in F9, you can see- WOW, that is cool, it's a dynamic array constant.
But this is an array formula here inside the ROW.
So I'm going to copy this, come over here, and highlight just that little bit, and Ctrl+V.
Now, there's a bunch of array calculations, 1-2-3, the IF is making a 4th, and then in the ROW is making another one.
That's a lot of array operations, Ctrl+Shift+Enter, and now I can see that THAT is working, if I change this back to 2.
There is still one OTHER way we could do this!
If I can only copy and paste fast enough, I'm going to Ctrl+C, C to copy, and load it into the clipboard.
I'm going to Ctrl+C, I'm going to Ctrl+C, Ctrl+C, Ctrl+C.
If we really didn't want to do Ctrl+Shift+Enter, we could avoid the IF and come over and inside of SUMPRODUCT, do the LARGE, but we're going to have to build the array with multiplication.
So I'm going to say "There's the first condition", close parentheses * open parentheses, and just build it!
With the three conditions using BOOLEAN, multiplying TRUE*TRUE*TRUE, and then what do we want?
We want this array right here.
So that's inside the array argument of LARGE, now I can do comma, and paste this, that'll be our dynamic "k", creating a dynamic array constant.
Close parentheses on the LARGE, close parentheses on the SUMPRODUCT, and because we're not using the IF, we're using BOOLEAN multiplying, Enter!
So now, if I change this right here, BOOM, back to 2.
Alright, throw back to MrExcel!
Bill: Hey Mike, alright, that is good.
Out of those three formulas I like the second one, the SUM with LARGE, and then the three IF statements inside seems easier to me, at least as easy as it could be with these array formulas.
For those of you watching, if you'd like to get really good at array formulas, you need to check out Mike's new book "Ctrl+Shift+Enter".
On Friday the 25th it should be hitting the first of the Amazon distribution centers.
It shipped from Chicago on Tuesday, that means on Thursday they'll get it down in Kentucky, and it should be for sale today, at Amazon at least, if you're in the center of the country.
If you're further out in one of the other distribution centers, it might take them a couple more days.
But go ahead and order, they will ship it to you soon as it hits your local distribution center, what a great book covering Excel array formulas.
Alright 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!
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun, this is our episode 133 - Sum the Top 2 that match Criteria!
Alright, question from Joe JG from YouTube: Wants to be able to SUM the top 2 values from this range, but only the ones that match this criteria.
Alright, so I'm going to go old school here, select the range, Data, Filter, Advanced Filter, we're going to Copy to another location.
The criteria range are these 6 cells right here, and we're going to copy it to there, click OK.
Alright, so now that extracted range are only the matching records, and then what is it, it's =MAX!
This will select a whole bunch of cells here, because you don't know how many records we're going to get.
Plus the second largest value is LARGE of that same range, ,2 . There we go, 95 which is the required answer.
Of course, this method works great right now, but if any of the criteria change, we got to go back and start all over again.
But Mike has something better that, Mike, let's see what you have!
Mike: Thanks MrExcel!
Hey, I got to tell you, this kind of problem here, you know, as I see it, has a bunch of array calculations, it's going to get pretty complicated.
This is totally awesome, Advanced Filter based on that criteria, and then MAX + LARGE too, got to love it, especially if this data set is just a static data set, it's not going to change, and that 2 is not going to change either.
Alright, so the LARGE function, why don't we take a look at that, it needs an array, but if I highlight this column right here, I really don't want all the numbers, I want just the ones that match these three criteria.
So I’m going to build an array calculation here.
Now I'll start with the IF, see if anything in here is equal to that month there, comma, now I need the value of TRUE, but there's still more conditions.
So you put another IF, nesting it, it's anything in this column equals to that, comma, there's still another condition, so value of TRUE.
We have to put a third IF, anything in there equal to this year, comma, and now we can put the value if TRUE.
These are three array calculations, all in all they will deliver TRUEs and FALSEs, only where there's a TRUE, a TRUE, and a TRUE, will it pick out from this column of numbers.
Now, the IF function, if you have an array calculation and the logical test, it doesn't matter where the rest of the formula is sitting, or what other function is on the outside of the IF, it WILL require Ctrl+Shift+Enter.
Alright, so I'm going to close this off, 1-2-3.
So there, I'm back to the LARGE, so I type a comma, and then- oh wait a second, I don't want 2, because that would just give me the second largest.
I'm going to do another array calculation using an array constant, {1,2} ! That says "Hey LARGE, give me one AND two largest!" And then I'm going to close off the LARGE.
Now if I highlight this, we'll see that the LARGE will deliver two values, i'll hit F9, BOOM, there it is, Ctrl+Z.
Now I can put it, you know what, what would happen if I put it in SUMPRODUCT?
Forget it, I would still have to use Ctrl+Shift+Enter, watch this!
Remember, the trump rule for the IF is, any array calculation and logical test, forget it, doesn't matter what functions on the outside is, when I hit Enter, it's not going to work, if I do Ctrl+Shift+Enter, we get our 95, but here's the deal!
I had to turn off Speak on Enter.
Let's be unambiguous here, in case someone else is looking at it, and they're thinking "I know SUMPRODUCT doesn't require Ctrl+Shift+Enter", let's not give them the chance to think that.
Let's put SUM, then we're looking at this, we know all we need to do Ctrl+Shift+Enter, when you do Ctrl+Shift+Enter, you're saying "Hey Excel, I'm doing an array formula", you can see up in the formula bar, those curly bracket, that's Excel telling you "Yes, I understood that that's an array operation." Now we could amend this, because Copy, Esc, what if we didn't?
Or we wanted this to be variable 2?
Well we'd have to get a little tricky here.
Now let me click Ctrl+Shift+Enter and just come off to the side.
I'm going to use the INDIRECT function, and I'm going to build that array constant, but it will be dynamic.
In double quotes I'm going to say "1:" . So that colon tells us we're going from row number 1 to some other row.
I'll use & to join and, for right now, it's from one to two.
Now INDIRECT this program to take a reference, that's text, if I F9, you can see it's text there, and convert it to an actual reference.
When I highlight this in F9, and won't let me because row 1 to 2 has too many references in it.
But no problem, we can tell the ROW function to look at that and tell me how many rows, well there's only two, 1 and 2.
So highlight this in F9, there it is.
Now I'm going to Ctrl+Shift+Enter, and change this to 3.
And now, if we come down here and highlight it in F9, you can see- WOW, that is cool, it's a dynamic array constant.
But this is an array formula here inside the ROW.
So I'm going to copy this, come over here, and highlight just that little bit, and Ctrl+V.
Now, there's a bunch of array calculations, 1-2-3, the IF is making a 4th, and then in the ROW is making another one.
That's a lot of array operations, Ctrl+Shift+Enter, and now I can see that THAT is working, if I change this back to 2.
There is still one OTHER way we could do this!
If I can only copy and paste fast enough, I'm going to Ctrl+C, C to copy, and load it into the clipboard.
I'm going to Ctrl+C, I'm going to Ctrl+C, Ctrl+C, Ctrl+C.
If we really didn't want to do Ctrl+Shift+Enter, we could avoid the IF and come over and inside of SUMPRODUCT, do the LARGE, but we're going to have to build the array with multiplication.
So I'm going to say "There's the first condition", close parentheses * open parentheses, and just build it!
With the three conditions using BOOLEAN, multiplying TRUE*TRUE*TRUE, and then what do we want?
We want this array right here.
So that's inside the array argument of LARGE, now I can do comma, and paste this, that'll be our dynamic "k", creating a dynamic array constant.
Close parentheses on the LARGE, close parentheses on the SUMPRODUCT, and because we're not using the IF, we're using BOOLEAN multiplying, Enter!
So now, if I change this right here, BOOM, back to 2.
Alright, throw back to MrExcel!
Bill: Hey Mike, alright, that is good.
Out of those three formulas I like the second one, the SUM with LARGE, and then the three IF statements inside seems easier to me, at least as easy as it could be with these array formulas.
For those of you watching, if you'd like to get really good at array formulas, you need to check out Mike's new book "Ctrl+Shift+Enter".
On Friday the 25th it should be hitting the first of the Amazon distribution centers.
It shipped from Chicago on Tuesday, that means on Thursday they'll get it down in Kentucky, and it should be for sale today, at Amazon at least, if you're in the center of the country.
If you're further out in one of the other distribution centers, it might take them a couple more days.
But go ahead and order, they will ship it to you soon as it hits your local distribution center, what a great book covering Excel array formulas.
Alright 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!
