Rahim needs a solution: "When I change the date, I need it to Sum all of the figures of that region - SUMIF is not working." Using =MATCH, =SUMIF, =OFFSET, =INDEX and more, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen come up with Formula based solutions to reach a solution for Rahim. Follow along with Episode #1707 and see how you can Sum a Range as Data is changed.
Dueling Excel Podcast #123...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #123...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel.com, I'll be joined by a Mike Girvin from Excel Is Fun, this is our episode 123 - Sum All 2-way Lookup Matches!
Here's a great question sent in from Pakistan, Rahim: "Requirements is that when I change the date, it can sum all the figures of that region.
SUMIFS is not working." Alright, so we need to go find all the things that are North from this particular column.
I'm going to use one little helper cell here, =MATCH.
MATCH looks forward that date within this range of columns up here, and we want ,0 , because we want an exact match.
That tells us that the date that we've entered is in the second column.
Now, I don't even have to use SUMIFS, I can use =SUMIF, the range that we want to look through.
When I look through this range here, comma, see if it's equal to North, comma, and then if it is, add up the corresponding cell from the SUM range, while the SUM range is the one that's going to change every single time.
So I'm going to say OFFSET of this range right here, comma, how many rows, well no rows, how many columns, the columns is the answer from right there.
So in other words, we're going 2 columns over from A2-A6.
So close the OFFSET, close the SUMIF, and Ctrl+Enter, there's our 16.
So let's just do a little check here, we have 5+6+5=16, excellent.
We will copy this date down here, and it changes to 28, 10+8+10, because we're now using an offset of 3.
Alright, I'm interested to see what Mike is going to come up with here, something a lot cooler than this.
Mike, let's see what you have!
Mike: Thanks MrExcel!
Cooler than this?
No way, this is amazing, I love this.
The OFFSET reference, it's an entire column, and then you go 0-3, boop-boop-boop, to look up that entire column are OFFSET to get the entire column.
Hey, if we're going to use a cool criteria, MrExcel, this is like an ice cube, and my solution's going to be like the Sun or something!
Alright, let's come over here.
So I'm going to take the approach of- hey, there's a 2-way range here!
I'm going to try and look up within a 2-way range, look up the entire column, the INDEX function is perfect for that.
I'm going to give it the 2-way array in the array argument there, comma, now let's think about this.
If I'm looking up the serial number within these serial numbers here, what I really want is that entire column.
But look, this column has all the rows, so the way you instruct row number to get all the rows, is either typing a 0 or leaving it empty, so I'm going to leave it empty.
Now column number, I'm going to use the same MATCH as MrExcel, lookup the serial number within these sorted serial number dates, and since they're sorted, I'm going to do approximate match, the default!
You can always tell that a function can take the default settings if you have those square brackets, and if you know it, you can leave it out, so I'm simply going to ), that'll do approximate match.
Now let's highlight this and use our F9 key, evaluate key, and I can see, sure enough, it's looking up the entire February column, Ctrl+Z.
Now I'm going to slap that into SUMIF.
Range- hey, there's all the criteria, comma, and the criteria is North, ). Now you'd think this argument right here, F9, hey, it's spitting out an array, and you think it need Ctrl+Shift+Enter.
And if you go look at MrExcel's too, that OFFSET is spitting out the same range of values, or looks like an array, I can hit Enter without Ctrl+Shift+Enter.
If I change this, sure enough, it's giving me the correct values.
Now let's check this out, I'm going to come to the SUMIF and click on that SUM range.
Yeah, that's when you hit F9, right?
It looks like it's spitting out a resultant array like you'd see in an array formula, or- So, let's look at this, if I leave this here and hit Enter, what happens?
You get the famous error message that doesn't really tell you the truth about what's going on.
The SUMIF function cannot handle arrays, meaning if you leave it as an array constant, or it was a resultant array from an array formula, SUMIF doesn't like that, Esc.
So why in the world does the output from INDEX and OFFSET work here?
Because OFFSET and INDEX are spitting out a range of values, and the SUMIF can handle a range like that.
Absolutely amazing.
Alright, throw it back to MrExcel!
Bill: As cool as the Sun, are you kidding me?
That INDEX with the ,0 , or just leave the thing blank to return entire column values right to this, I mean that, my friend, was cool!
Alright, 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.com, I'll be joined by a Mike Girvin from Excel Is Fun, this is our episode 123 - Sum All 2-way Lookup Matches!
Here's a great question sent in from Pakistan, Rahim: "Requirements is that when I change the date, it can sum all the figures of that region.
SUMIFS is not working." Alright, so we need to go find all the things that are North from this particular column.
I'm going to use one little helper cell here, =MATCH.
MATCH looks forward that date within this range of columns up here, and we want ,0 , because we want an exact match.
That tells us that the date that we've entered is in the second column.
Now, I don't even have to use SUMIFS, I can use =SUMIF, the range that we want to look through.
When I look through this range here, comma, see if it's equal to North, comma, and then if it is, add up the corresponding cell from the SUM range, while the SUM range is the one that's going to change every single time.
So I'm going to say OFFSET of this range right here, comma, how many rows, well no rows, how many columns, the columns is the answer from right there.
So in other words, we're going 2 columns over from A2-A6.
So close the OFFSET, close the SUMIF, and Ctrl+Enter, there's our 16.
So let's just do a little check here, we have 5+6+5=16, excellent.
We will copy this date down here, and it changes to 28, 10+8+10, because we're now using an offset of 3.
Alright, I'm interested to see what Mike is going to come up with here, something a lot cooler than this.
Mike, let's see what you have!
Mike: Thanks MrExcel!
Cooler than this?
No way, this is amazing, I love this.
The OFFSET reference, it's an entire column, and then you go 0-3, boop-boop-boop, to look up that entire column are OFFSET to get the entire column.
Hey, if we're going to use a cool criteria, MrExcel, this is like an ice cube, and my solution's going to be like the Sun or something!
Alright, let's come over here.
So I'm going to take the approach of- hey, there's a 2-way range here!
I'm going to try and look up within a 2-way range, look up the entire column, the INDEX function is perfect for that.
I'm going to give it the 2-way array in the array argument there, comma, now let's think about this.
If I'm looking up the serial number within these serial numbers here, what I really want is that entire column.
But look, this column has all the rows, so the way you instruct row number to get all the rows, is either typing a 0 or leaving it empty, so I'm going to leave it empty.
Now column number, I'm going to use the same MATCH as MrExcel, lookup the serial number within these sorted serial number dates, and since they're sorted, I'm going to do approximate match, the default!
You can always tell that a function can take the default settings if you have those square brackets, and if you know it, you can leave it out, so I'm simply going to ), that'll do approximate match.
Now let's highlight this and use our F9 key, evaluate key, and I can see, sure enough, it's looking up the entire February column, Ctrl+Z.
Now I'm going to slap that into SUMIF.
Range- hey, there's all the criteria, comma, and the criteria is North, ). Now you'd think this argument right here, F9, hey, it's spitting out an array, and you think it need Ctrl+Shift+Enter.
And if you go look at MrExcel's too, that OFFSET is spitting out the same range of values, or looks like an array, I can hit Enter without Ctrl+Shift+Enter.
If I change this, sure enough, it's giving me the correct values.
Now let's check this out, I'm going to come to the SUMIF and click on that SUM range.
Yeah, that's when you hit F9, right?
It looks like it's spitting out a resultant array like you'd see in an array formula, or- So, let's look at this, if I leave this here and hit Enter, what happens?
You get the famous error message that doesn't really tell you the truth about what's going on.
The SUMIF function cannot handle arrays, meaning if you leave it as an array constant, or it was a resultant array from an array formula, SUMIF doesn't like that, Esc.
So why in the world does the output from INDEX and OFFSET work here?
Because OFFSET and INDEX are spitting out a range of values, and the SUMIF can handle a range like that.
Absolutely amazing.
Alright, throw it back to MrExcel!
Bill: As cool as the Sun, are you kidding me?
That INDEX with the ,0 , or just leave the thing blank to return entire column values right to this, I mean that, my friend, was cool!
Alright, 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!
