Anthony sent Mike Girvin a workbook and a question. We have a Received Date, the Quantity and other Data. What we need is to determine is for Each Weekday, how many Quantity came in? Sounds simple, right? It is not. Extra columns of Data, improperly Formatted Range Fields and the like make this a perfect opportunity for Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen to strut their talents. Follow along with Episode #1721 as Dueling Excel looks at the matter of finding Quantity by Day.
Dueling Excel Podcast #122...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 #122...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, will be joined by Mike Girvin from Excel Is Fun, this is our episode 126 - It Sounds so Simple - Quantity by Day!
Mike sent this one over, this is Anthony at Highline asked this question.
He has, it looks like, 37 rooms here, here's the date that it was received, and the quantity.
And then for each weekday, we want to know how many quantity came in there.
Boy, there's just so many issues here.
Issue number one is, we don't have a single range in order to get all this to fit on one screen.
Anthony, or whoever Anthony works for, has two columns, alright, so we have to look through these dates, we have to look through these days.
The other issue, these aren't real dates, this is text!
This is FRI_5/17, oh my god!
Anthony, really?
Come on, let's format this.
We can make it look like you want it to look like, but let's really do this as date.
So I'm proposing putting in 5/17, OK.
I understand that's not what you wanted to look like, Ctrl+1, and then we come in here, Custom, and we say "DDD M/D".
Alright, so I'm proposing converting everything, and actually in my version, I have converted all of those two dates rather than leave them as text.
Right now, over here off to the side, I'm going to create some helper columns.
The helper column says "We're going to get the weekday of D2, comma, and that's going to give us a number from 1-7".
But hey, the blanks come in with a weekday, because the blank, you know, that's January 1st 1900, and we're getting a weekday there.
So, I need to make sure that if the cell is blank I'm getting 0.
So I'm multiplying it by NOT(ISBLANK).
If it is blank, that's TRUE*NOT, and what that does is it zeroes things out, so if there is no date, I'm getting a zero for the weekday, instead of the number 1-7.
I'm also careful here to use the ,2 which gives me the numbers 1-7 instead of 0-6 that you can get in some of those.
And then the quantity here is just simple, copy that over, so we'll copy this down through all of the rooms, through all of the data right there, Ctrl+V.
Alright, now I want to take this weekday calculation and copy it over here, and it's not the right number of columns over, so it's pointing at the wrong place.
But I can simply drag the blue box there to point to the right place, and then also here, see, because there are two things pointing to G2, I won't able to drag one of them.
So I'm going to have to manually edit that one, and then the quantity, simple enough, just point to this quantity here.
Alright, copy this down, down through room 37 on the second set of columns.
And now that I have this, I'm going to Ctrl+X to cut, go down to the bottom, and Ctrl+V to paste.
Now I have a beautiful nice little 1/1 range, so I just have to look through, it all becomes very, very simple.
From this point put the numbers here, 1-2-3-4-5-6-7, I see the values above changing, this must be some random things equal.
I'm going to use SUMIF, I know I could use SUMIFS, but I'm using SUMIF.
I look through these weekday numbers over here, F4, comma, see if it's equal to the value just below me that I typed in, comma, if it is, give me the corresponding value from this column over here, F4.
Alright, so there's our 39, 35 on Tuesday, not on Wednesday, not on Thursday, some on Friday, some on Saturday, some on Sunday, there you go.
Alright so, boy, just you know, I realized, you know, you get a job, you go in, and someone created this worksheet, and it can be an ugly, ugly worksheet, with the text and things like that.
So lots of work around here just to get those formulas to work.
Of course, I know Mike will knock at some insane array formula, that's what he tends to do, because he's the author of the best-selling book on array formulas in the planet.
So Mike, let's see what you have!
Mike: Thanks MrExcel!
The best-selling author on array formulas on the planet.
You know how many books have sold, ZERO!
Wow, zero makes you the best- oh my heavens!
You know, I'm not going to use an array formula here.
Now the thing about this is, is Anthony was in my class at Highline, and he sent me this template, and I just sent him an email back that says "Oh, THIS is not a well-made spreadsheet!" So what I'm going to do here is, I'm just going to deal with the fact that this is text, and build a formula from that point of view.
Now I'm going to also notice that I have these 3-letter abbreviations for the day here, and it looks like all of them text dates also have that 3-letter abbreviation.
You know, well look, there's a 4-letter, so they've been consistent, right?
Monday-Friday over here, we have a Tuesday.
So I'm going to use this text here as the criteria, and then just do some sort of SUMIF based on these text dates.
So I'm going to use the SUMIF, the range, that has all the criteria.
Now, there's two different columns, so I'm actually going to just use two different SUMIF formulas.
So I got that range right there, I'm going to F4 to lock it, comma in the criteria.
Well, if I click right here, it'll get zero matches, but I'm noticing the pattern, there's always the abbreviation for the day, and then some stuff after.
So I'm going to join to this, using Shift+7, the &, a wild card, and the wild card has to be in double quotes.
Now that asterisk is a wild card that says "Hey give me zero or more characters." So it could see T-U-E-S, or T-U-E-S- , or T-UES- , and then whatever.
Alright, so that'll be our criteria, and then a comma to get to that SUM range.
Now, I'm actually going to copy this and use this a second time, Ctrl+C, the SUM range is going to be this range right here.
Alright, F4 to lock it.
Alright, so that's the first one, and then +SUMIF, and the range that's going to be over here.
And it's a slightly different size range, but no problem.
F4 to lock it, Ctrl+V, comma, oops, already had a comma, and then I'll get this second SUM range here, F4 to lock it, and that formula should do it.
Really, it's just that Tuesday with any text to afterwards(?), Ctrl+Enter.
So there we get our 35.
Now I'm going to actually use the Ctrl key and click, click, I'm holding Ctrl down and selecting cells not next to each other, and I'm going to make sure that the last cell I select is that cell, hit F2.
And now I'm going to populate this formula into all of these non-contiguous cells by holding Ctrl+Enter!
So there we go.
Alright, throw it back to MrExcel!
Bill: Always, always interesting things there Mr. Girvin, I love that, the wild card.
You know, I've done greater than ampersand, ">"& some number before as a criteria, because it makes perfect sense that the wild card would work.
The cool trick there for the pasting, we did non-contiguous cells.
And you know, it's not the number of books you sold right now, it's the number of pre-orders, and based on the pre-orders Mike, "Ctrl+Shift+Enter" is still the world's best-selling book on Excel array formulas, so can't wait to get my copy.
Alright, well 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 126 - It Sounds so Simple - Quantity by Day!
Mike sent this one over, this is Anthony at Highline asked this question.
He has, it looks like, 37 rooms here, here's the date that it was received, and the quantity.
And then for each weekday, we want to know how many quantity came in there.
Boy, there's just so many issues here.
Issue number one is, we don't have a single range in order to get all this to fit on one screen.
Anthony, or whoever Anthony works for, has two columns, alright, so we have to look through these dates, we have to look through these days.
The other issue, these aren't real dates, this is text!
This is FRI_5/17, oh my god!
Anthony, really?
Come on, let's format this.
We can make it look like you want it to look like, but let's really do this as date.
So I'm proposing putting in 5/17, OK.
I understand that's not what you wanted to look like, Ctrl+1, and then we come in here, Custom, and we say "DDD M/D".
Alright, so I'm proposing converting everything, and actually in my version, I have converted all of those two dates rather than leave them as text.
Right now, over here off to the side, I'm going to create some helper columns.
The helper column says "We're going to get the weekday of D2, comma, and that's going to give us a number from 1-7".
But hey, the blanks come in with a weekday, because the blank, you know, that's January 1st 1900, and we're getting a weekday there.
So, I need to make sure that if the cell is blank I'm getting 0.
So I'm multiplying it by NOT(ISBLANK).
If it is blank, that's TRUE*NOT, and what that does is it zeroes things out, so if there is no date, I'm getting a zero for the weekday, instead of the number 1-7.
I'm also careful here to use the ,2 which gives me the numbers 1-7 instead of 0-6 that you can get in some of those.
And then the quantity here is just simple, copy that over, so we'll copy this down through all of the rooms, through all of the data right there, Ctrl+V.
Alright, now I want to take this weekday calculation and copy it over here, and it's not the right number of columns over, so it's pointing at the wrong place.
But I can simply drag the blue box there to point to the right place, and then also here, see, because there are two things pointing to G2, I won't able to drag one of them.
So I'm going to have to manually edit that one, and then the quantity, simple enough, just point to this quantity here.
Alright, copy this down, down through room 37 on the second set of columns.
And now that I have this, I'm going to Ctrl+X to cut, go down to the bottom, and Ctrl+V to paste.
Now I have a beautiful nice little 1/1 range, so I just have to look through, it all becomes very, very simple.
From this point put the numbers here, 1-2-3-4-5-6-7, I see the values above changing, this must be some random things equal.
I'm going to use SUMIF, I know I could use SUMIFS, but I'm using SUMIF.
I look through these weekday numbers over here, F4, comma, see if it's equal to the value just below me that I typed in, comma, if it is, give me the corresponding value from this column over here, F4.
Alright, so there's our 39, 35 on Tuesday, not on Wednesday, not on Thursday, some on Friday, some on Saturday, some on Sunday, there you go.
Alright so, boy, just you know, I realized, you know, you get a job, you go in, and someone created this worksheet, and it can be an ugly, ugly worksheet, with the text and things like that.
So lots of work around here just to get those formulas to work.
Of course, I know Mike will knock at some insane array formula, that's what he tends to do, because he's the author of the best-selling book on array formulas in the planet.
So Mike, let's see what you have!
Mike: Thanks MrExcel!
The best-selling author on array formulas on the planet.
You know how many books have sold, ZERO!
Wow, zero makes you the best- oh my heavens!
You know, I'm not going to use an array formula here.
Now the thing about this is, is Anthony was in my class at Highline, and he sent me this template, and I just sent him an email back that says "Oh, THIS is not a well-made spreadsheet!" So what I'm going to do here is, I'm just going to deal with the fact that this is text, and build a formula from that point of view.
Now I'm going to also notice that I have these 3-letter abbreviations for the day here, and it looks like all of them text dates also have that 3-letter abbreviation.
You know, well look, there's a 4-letter, so they've been consistent, right?
Monday-Friday over here, we have a Tuesday.
So I'm going to use this text here as the criteria, and then just do some sort of SUMIF based on these text dates.
So I'm going to use the SUMIF, the range, that has all the criteria.
Now, there's two different columns, so I'm actually going to just use two different SUMIF formulas.
So I got that range right there, I'm going to F4 to lock it, comma in the criteria.
Well, if I click right here, it'll get zero matches, but I'm noticing the pattern, there's always the abbreviation for the day, and then some stuff after.
So I'm going to join to this, using Shift+7, the &, a wild card, and the wild card has to be in double quotes.
Now that asterisk is a wild card that says "Hey give me zero or more characters." So it could see T-U-E-S, or T-U-E-S- , or T-UES- , and then whatever.
Alright, so that'll be our criteria, and then a comma to get to that SUM range.
Now, I'm actually going to copy this and use this a second time, Ctrl+C, the SUM range is going to be this range right here.
Alright, F4 to lock it.
Alright, so that's the first one, and then +SUMIF, and the range that's going to be over here.
And it's a slightly different size range, but no problem.
F4 to lock it, Ctrl+V, comma, oops, already had a comma, and then I'll get this second SUM range here, F4 to lock it, and that formula should do it.
Really, it's just that Tuesday with any text to afterwards(?), Ctrl+Enter.
So there we get our 35.
Now I'm going to actually use the Ctrl key and click, click, I'm holding Ctrl down and selecting cells not next to each other, and I'm going to make sure that the last cell I select is that cell, hit F2.
And now I'm going to populate this formula into all of these non-contiguous cells by holding Ctrl+Enter!
So there we go.
Alright, throw it back to MrExcel!
Bill: Always, always interesting things there Mr. Girvin, I love that, the wild card.
You know, I've done greater than ampersand, ">"& some number before as a criteria, because it makes perfect sense that the wild card would work.
The cool trick there for the pasting, we did non-contiguous cells.
And you know, it's not the number of books you sold right now, it's the number of pre-orders, and based on the pre-orders Mike, "Ctrl+Shift+Enter" is still the world's best-selling book on Excel array formulas, so can't wait to get my copy.
Alright, well 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!
