Today's Dueling Excel Podcast is a question sent in by Hassan. Hassan wants to enter a name and a through-month and then add up that person's sales from January through the selected month. Mike and Bill show various methods in Episode 1134.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, it's another Dueling Excel podcast, that must mean it's Friday. I'm Bill Jelen from MrExcel, we’ll be joined by Mike Girvin from ExcelIsFun at YouTube. Mike sent me this question that came in to him from Hassan at YouTube. Frankly, Mike I don't have a clue what you're going to do after I solved this, because there's only one way to solve this problem. Hassan wants to know, he puts in a name and a through date, and he wants to add up all the sales through that particular column. So I know, I colored this red, I know we're going to use an OFFSET based on that cell.
Now let's try and do a couple things here, which row are we going to look for? Well, that's going to be pretty easy, that's the MATCH function, the MATCH of the name here in that set of cells, we'll press F4 ,0 , because we want an exact match. And so Fred is in row 6, easy, right? Columns, alright, well, we could use MATCH again, but I'm going to try and do something a little bit more bizarre, I'm going to use =DATEVALUE. The DATEVALUE of this day, May, &” 1, 2009”, does not matter what year we use there, because I'm going to take that date and then use the MONTH function, the MONTH function, of course, will return a number from 1 to 12.
OK so now, I know how many rows down and how many columns across, so my answer is going to be =OFFSET. Let's start from the corner cell, how many rows down, well that's our answer that we got right up here, how many columns over, I'm going to go over 1 column.
And then how many rows tall, just 1, how many columns, well that's where we use this answer right here. Now that's going to return, in this case, you know, five or six cells, so we need to wrap that whole thing in the SUM function, and we end up with our answer of 14, let’s just do a little test here, there's our 14. Let's put someone else in, we'll put Joe and December, and our answer here, 21, Joe, all the way out through December and 21, it's working! Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, this is great question!
I'll do a slight variation on the formula that MrExcel used, using SUM and OFFSET. I’m going to use Alt+= for AutoSum, and then OFFSET.
Instead of the starting reference being here, I'm going to start right here, that'll help us out later on, avoid one argument. So we'll start there, and then how many rows do we need to go down, I'm going to use that MATCH.
I'm going to say lookup SUE, comma, within this whole range right here, ,0 , close parentheses, that MATCH is going to say how many rows down we have to go, now that’ll be 1-2-3. Comma, columns, that's how many columns over from this position, we don't need any because we started here, by default if we leave that out, it'll be assumed at 0, so that's perfect.
Height is 1, if we leave it out by default it assumes it's 1, so I'm going to hit comma.
And width, we'll use another MATCH, I'm going to say “Lookup March”, comma, within this range here, that DATEVALUE function formula with a month that MrExcel used was just awesome.
However if you don't have dates, you can go ahead and use a second MATCH. All right, so that'll do it, I'll close parentheses, we have all the arguments just slightly different than the way MrExcel did it. $6, and if we change this and test it, sure enough, it's going to work.
Ah, but don't believe it for a second, there's always more than one way to do it, I'll show you a different way here. Pretty bizarre, it will avoid using the OFFSET though, which is a volatile function, and some people don't like volatile functions. What is the INDEX function? Now, you've seen it probably many videos on 1-way and 2-way lookup with INDEX.
I'm going to just quickly, actually copy this little piece right here, because we're going to use that a couple times in this formula right here, =INDEX. I'm going to look up in this range, and I'm going to see if I can return that 5 right there using INDEX. So that's the array, the ordinal position will be that MATCH, and sure enough we get a 5, it's looking up in that range and finding that 5.
Now, I want to try and find this 4, now this is going to involve doing a 2-way lookup, because it's in the middle of the table. So =INDEX, I'm going to highlight the whole table, comma, and we need a row and a column number.
I already have the row, which is, Ctrl+V, that MATCH, comma, and now the column will use that MATCH and look up this little piece right here, comma, within this range right here, ,0 , close parenthesis. So we have our column number and a row number in our INDEX, so INDEX, we're just using this for a 2-way lookup, we got the 4 and the 5. Now, is that bizarre? If you copy this INDEX right here, Ctrl+C, actually I'm going to Ctrl+C C and open up the clipboard. Let's try that again, Ctrl+C C Esc, and then I'm going to copy this.
Now why in the world am I doing that?
INDEX is looking up this value here and this value here. Well, you can make the INDEX function lookup not the value, but the cell reference, and that will be volatile, because right now, if we use it this way, it will give us B10:D10, which will work inside of our SUM function.
I'm going to click here and Alt+=, I'm going to highlight this range here, now notice that the SUM function has a cell reference, a colon, and a cell reference. If we highlight that first cell reference by double-clicking and click on this, it inserts the INDEX in place of the cell reference. Double-click here and put that second INDEX, we are putting the INDEX function into the context of being a cell reference, there's a colon there. So immediately the INDEX goes “Ah, I'm not going to return a value, but instead I'm going to return a cell reference.” Enter, and we can see we get the same value, if we change this to February, we see just that, we get the same number.
Now, let's run formula evaluate, Alt T U F, TUF, and let's just evaluate this. You can see, when we evaluate it we get a B10 for that first INDEX, and when we evaluate this one down we get a C10. It's all because of that colon, and INDEX is functioning as retrieving the cell reference instead of the value. Alright, there's a couple ways to do that, we'll see you next trick!
Bill: That's amazing! INDEX and an INDEX with a colon in between, and it gives you the range, unbelievable! There you go. Hey, thanks for stopping by, everyone learned something on this one, point to Mike for that great idea, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, it's another Dueling Excel podcast, that must mean it's Friday. I'm Bill Jelen from MrExcel, we’ll be joined by Mike Girvin from ExcelIsFun at YouTube. Mike sent me this question that came in to him from Hassan at YouTube. Frankly, Mike I don't have a clue what you're going to do after I solved this, because there's only one way to solve this problem. Hassan wants to know, he puts in a name and a through date, and he wants to add up all the sales through that particular column. So I know, I colored this red, I know we're going to use an OFFSET based on that cell.
Now let's try and do a couple things here, which row are we going to look for? Well, that's going to be pretty easy, that's the MATCH function, the MATCH of the name here in that set of cells, we'll press F4 ,0 , because we want an exact match. And so Fred is in row 6, easy, right? Columns, alright, well, we could use MATCH again, but I'm going to try and do something a little bit more bizarre, I'm going to use =DATEVALUE. The DATEVALUE of this day, May, &” 1, 2009”, does not matter what year we use there, because I'm going to take that date and then use the MONTH function, the MONTH function, of course, will return a number from 1 to 12.
OK so now, I know how many rows down and how many columns across, so my answer is going to be =OFFSET. Let's start from the corner cell, how many rows down, well that's our answer that we got right up here, how many columns over, I'm going to go over 1 column.
And then how many rows tall, just 1, how many columns, well that's where we use this answer right here. Now that's going to return, in this case, you know, five or six cells, so we need to wrap that whole thing in the SUM function, and we end up with our answer of 14, let’s just do a little test here, there's our 14. Let's put someone else in, we'll put Joe and December, and our answer here, 21, Joe, all the way out through December and 21, it's working! Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, this is great question!
I'll do a slight variation on the formula that MrExcel used, using SUM and OFFSET. I’m going to use Alt+= for AutoSum, and then OFFSET.
Instead of the starting reference being here, I'm going to start right here, that'll help us out later on, avoid one argument. So we'll start there, and then how many rows do we need to go down, I'm going to use that MATCH.
I'm going to say lookup SUE, comma, within this whole range right here, ,0 , close parentheses, that MATCH is going to say how many rows down we have to go, now that’ll be 1-2-3. Comma, columns, that's how many columns over from this position, we don't need any because we started here, by default if we leave that out, it'll be assumed at 0, so that's perfect.
Height is 1, if we leave it out by default it assumes it's 1, so I'm going to hit comma.
And width, we'll use another MATCH, I'm going to say “Lookup March”, comma, within this range here, that DATEVALUE function formula with a month that MrExcel used was just awesome.
However if you don't have dates, you can go ahead and use a second MATCH. All right, so that'll do it, I'll close parentheses, we have all the arguments just slightly different than the way MrExcel did it. $6, and if we change this and test it, sure enough, it's going to work.
Ah, but don't believe it for a second, there's always more than one way to do it, I'll show you a different way here. Pretty bizarre, it will avoid using the OFFSET though, which is a volatile function, and some people don't like volatile functions. What is the INDEX function? Now, you've seen it probably many videos on 1-way and 2-way lookup with INDEX.
I'm going to just quickly, actually copy this little piece right here, because we're going to use that a couple times in this formula right here, =INDEX. I'm going to look up in this range, and I'm going to see if I can return that 5 right there using INDEX. So that's the array, the ordinal position will be that MATCH, and sure enough we get a 5, it's looking up in that range and finding that 5.
Now, I want to try and find this 4, now this is going to involve doing a 2-way lookup, because it's in the middle of the table. So =INDEX, I'm going to highlight the whole table, comma, and we need a row and a column number.
I already have the row, which is, Ctrl+V, that MATCH, comma, and now the column will use that MATCH and look up this little piece right here, comma, within this range right here, ,0 , close parenthesis. So we have our column number and a row number in our INDEX, so INDEX, we're just using this for a 2-way lookup, we got the 4 and the 5. Now, is that bizarre? If you copy this INDEX right here, Ctrl+C, actually I'm going to Ctrl+C C and open up the clipboard. Let's try that again, Ctrl+C C Esc, and then I'm going to copy this.
Now why in the world am I doing that?
INDEX is looking up this value here and this value here. Well, you can make the INDEX function lookup not the value, but the cell reference, and that will be volatile, because right now, if we use it this way, it will give us B10:D10, which will work inside of our SUM function.
I'm going to click here and Alt+=, I'm going to highlight this range here, now notice that the SUM function has a cell reference, a colon, and a cell reference. If we highlight that first cell reference by double-clicking and click on this, it inserts the INDEX in place of the cell reference. Double-click here and put that second INDEX, we are putting the INDEX function into the context of being a cell reference, there's a colon there. So immediately the INDEX goes “Ah, I'm not going to return a value, but instead I'm going to return a cell reference.” Enter, and we can see we get the same value, if we change this to February, we see just that, we get the same number.
Now, let's run formula evaluate, Alt T U F, TUF, and let's just evaluate this. You can see, when we evaluate it we get a B10 for that first INDEX, and when we evaluate this one down we get a C10. It's all because of that colon, and INDEX is functioning as retrieving the cell reference instead of the value. Alright, there's a couple ways to do that, we'll see you next trick!
Bill: That's amazing! INDEX and an INDEX with a colon in between, and it gives you the range, unbelievable! There you go. Hey, thanks for stopping by, everyone learned something on this one, point to Mike for that great idea, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
