In Today's Dueling Podcast Mike 'ExcelIsFun' Girvin and Bill 'MrExcel' Jelen look at counting all of the records that fall in a certain year and month. Episode #1349 offers two formula methods to get the answers.
Transcript of the video:
Hey, welcome back hits another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from Excel Is Fun.
This week's question sounded really, really easy, but it's not.
We have a year and a month and a whole bunch of records down here it goes from row 7 down to row 210.
May figure out, how many of those records fall in that month?
You know, I thought there would be a lot of different ways to do this.
Here's why I came up with, I'm going to create a little lookup table over here.
Starts at the date of 0, gives us a value of zero.
Now, here I want to put in the date the first of the month.
So, I'm going to use equal DATE VALUES and tell you what, let's make the Excel window a little bit smaller here, so we can fit that, in the window.
Scroll a little bit, equal DATEVALUE.
Now, date value converts text that looks like a date, into a real date and one of the things I've noticed is that they can deal with something like April, space, 1, comma, space, 2010.
So, I'm going to use, cell B4, ampersand, a comma, space Say space 1, comma, space, ampersand that year over there and that should convert those words, words numbers into a real date, okay!
Let's change that to a short date sure enough, 4/1/2011.
Let's try look up to another month, May.
Beautiful, it's working and then so, anything that is greater than or equal to that date gets a value of one up to the 1st of the next month.
So, equal EOMONTH zero, that's going to get me to, in this case, May 31st.
So, I add 1 and I'll get June 1st.
So, anything beyond that gets a zero.
All right! So, now it's simple.
I'm here have to do a vlookup into all of those dates.
Notice, I said vlookup, but unfortunately vlookup doesn't work in an array formula.
So, I'm going to go back to the old, old, old, old, old, never use it.
But in this case, certainly use it look up.
Look up say want to look up all of those dates, comma and this little table here.
It always is a comma true version.
We can just stop right there.
So, that's going to do all those lookups of course, we are going to get zeros and ones there, so I need just sum that whole thing and then [ control shift enter ], bam!
May 2011, 14.
Let's try April, 2011.
5, it is working.
All right! Mike, let's see what you have.
Mike: We have our little data set here and the only way I know how to take criteria inputs like this and compare it to a column of serial numbers is to use the text function, let's just see what happens if I use the text function and it's expecting a value, any number.
This is a serial number under their number of days since December 31st, 1899.
So, I'm going to say hey, take that value and format it.
Now format text requires that you know, custom number format, and the custom number format has to be in double quotes.
But day custom number format is easy.
I'm just going to say, year year year.
That will give me a year in four digits and then mmm that gives me the abbreviation, the word, three character abbreviation of the month and right now, I can see if I highlight this in hit [ F9 ], perfect from that, I now have something that can be compared to these two criteria.
[ Control Z ], now instead of just that single cell, I'm going to click in this top cell.
[ CTRL Shift down arrow ], [ F4 ] to jump the screen back up.
Now, that will give me an array of values and this text function.
I'm going to put inside of the sum-product because sum-product can handle an array, but this array right here and say hey, is anything in that array equal to this and then the join symbol ampersand, [ shift 7 ] and that right now if I highlight this, I get a series of trues and falses.
[ Control Z ], trues and falses cannot be counted by sum-product but no problem.
I have slept in essence this array right there, in that array argument can handle arrays.
But I need those trues and falses to be ones and zeros.
So, I'm going to use double negative to convert the trues and zeroes to ones and false and I want this equal sign to calculate, before that unitary operator, a negative sign, double negative and so I have to put that in parentheses, close parenthesis and COUNT, if I change this to 2011, I get 5.
All right throw it back over to MrExcel.
MrExcel: Hey, all right! Mike, that was cool. I love that.
Solving a problem, I was dealing with just formatting the whole thing as text.
Simple sum-product, much easier than my way to go.
I thank everyone for stopping by, See you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel.
I'll be joined by Mike Girvin from Excel Is Fun.
This week's question sounded really, really easy, but it's not.
We have a year and a month and a whole bunch of records down here it goes from row 7 down to row 210.
May figure out, how many of those records fall in that month?
You know, I thought there would be a lot of different ways to do this.
Here's why I came up with, I'm going to create a little lookup table over here.
Starts at the date of 0, gives us a value of zero.
Now, here I want to put in the date the first of the month.
So, I'm going to use equal DATE VALUES and tell you what, let's make the Excel window a little bit smaller here, so we can fit that, in the window.
Scroll a little bit, equal DATEVALUE.
Now, date value converts text that looks like a date, into a real date and one of the things I've noticed is that they can deal with something like April, space, 1, comma, space, 2010.
So, I'm going to use, cell B4, ampersand, a comma, space Say space 1, comma, space, ampersand that year over there and that should convert those words, words numbers into a real date, okay!
Let's change that to a short date sure enough, 4/1/2011.
Let's try look up to another month, May.
Beautiful, it's working and then so, anything that is greater than or equal to that date gets a value of one up to the 1st of the next month.
So, equal EOMONTH zero, that's going to get me to, in this case, May 31st.
So, I add 1 and I'll get June 1st.
So, anything beyond that gets a zero.
All right! So, now it's simple.
I'm here have to do a vlookup into all of those dates.
Notice, I said vlookup, but unfortunately vlookup doesn't work in an array formula.
So, I'm going to go back to the old, old, old, old, old, never use it.
But in this case, certainly use it look up.
Look up say want to look up all of those dates, comma and this little table here.
It always is a comma true version.
We can just stop right there.
So, that's going to do all those lookups of course, we are going to get zeros and ones there, so I need just sum that whole thing and then [ control shift enter ], bam!
May 2011, 14.
Let's try April, 2011.
5, it is working.
All right! Mike, let's see what you have.
Mike: We have our little data set here and the only way I know how to take criteria inputs like this and compare it to a column of serial numbers is to use the text function, let's just see what happens if I use the text function and it's expecting a value, any number.
This is a serial number under their number of days since December 31st, 1899.
So, I'm going to say hey, take that value and format it.
Now format text requires that you know, custom number format, and the custom number format has to be in double quotes.
But day custom number format is easy.
I'm just going to say, year year year.
That will give me a year in four digits and then mmm that gives me the abbreviation, the word, three character abbreviation of the month and right now, I can see if I highlight this in hit [ F9 ], perfect from that, I now have something that can be compared to these two criteria.
[ Control Z ], now instead of just that single cell, I'm going to click in this top cell.
[ CTRL Shift down arrow ], [ F4 ] to jump the screen back up.
Now, that will give me an array of values and this text function.
I'm going to put inside of the sum-product because sum-product can handle an array, but this array right here and say hey, is anything in that array equal to this and then the join symbol ampersand, [ shift 7 ] and that right now if I highlight this, I get a series of trues and falses.
[ Control Z ], trues and falses cannot be counted by sum-product but no problem.
I have slept in essence this array right there, in that array argument can handle arrays.
But I need those trues and falses to be ones and zeros.
So, I'm going to use double negative to convert the trues and zeroes to ones and false and I want this equal sign to calculate, before that unitary operator, a negative sign, double negative and so I have to put that in parentheses, close parenthesis and COUNT, if I change this to 2011, I get 5.
All right throw it back over to MrExcel.
MrExcel: Hey, all right! Mike, that was cool. I love that.
Solving a problem, I was dealing with just formatting the whole thing as text.
Simple sum-product, much easier than my way to go.
I thank everyone for stopping by, See you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
