Today, we have a database of date/time data. You don't care about time or even the day of the month - you just want to create a summary of how many records there are by month. This episode of the podcast offers two solutions - an array formula to count records by month or a pivot table that groups the date information up to month and year.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1771.
Count by Month.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in via YouTube, has dates, not just dates, dates and time check this out hundreds of rows of this and needs to create a summary table that shows how many records per month says, "I don't care about the time, don't care about what day of the month, just want to go back to the month." All right, it's not a Friday, it's not a dueling Excel podcast, but I'm still gonna be channeling Mike Excel Is Fun Girvin here because I got this trick from his book.
If we have to do this with a formula, we're going to have to use and IF inside when IF.
So, the first thing we do check to see if the month have all of these records over here, and I put dollar signs in using the F4 key as equal to the month of this cell D2.
If that's true, then we start a brand new IF statement you're saying wait a second.
Why don't we use the AND function because Mike Girvin says, we can't turns out if you're using an array function and this is an array function were we're evaluating a lot of rows in each function and does not work.
I also tried EO month, thinking that would ease it, but no with array functions you're not to do that.
So, first thing we do check to see if the month of all of those is equal to the month of this, then if that's True, see if the year of all of those cells is equal to the year of D2.
if that's True, put a 1 otherwise 0, otherwise 0.
All right! So, that returns inthis case it looks like 374 different results either 1 or 0.
So, we need to wrap those in another function equals SUM we'll do it.
So, add up all the ones and zeros.
Now, can we just press Enter here you see it doesn't work.
You have to hold down Control and Shift and press Enter to make that into an array formula, double click to shoot it down and you're good to go.
That's a confusing formula, it'd be much easier you know I'm guessing this this data's not really going to change it's not like someone's going to come in here and say, oh, no this really happened at February 5th it would be so much easier.
If we could just create a pivot table, 9 mouse clicks in fact.
So, let's do that choose one cell in the data, Insert, Pivot Table, OK and we're going to put date down the left hand side and we're also gonna put date in the value area and Excel is gonna be smart enough to know that we want to count that and then choose the very first date cell, very important we do that under Options we're gonna choose Group Field and choose Months and Years, click OK and we're done.
Now, for each year we get to see by month.
How many records there were.
If you want to see a total by year, well that's kind of a weird bug there I've talked about this before going to Options, Field Settings and turn the Subtotals to Automatic to get the yearly totals to appear as well.
Okay so, your choice either array formula from the book, Control+Shift+Enter or a Pivot Table the array formula will update if something here would change, then you know it will automatically update and I guess that's a good thing, but if your data's not going to change boy, 9 clicks to create a Pivot Table seems to be the way to go.
Oh! Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
So, hey there's a lot of ways to connect with us here at MrExcel.
If you're looking for an Excel book, DVD, e book, something for your Kindle for your iPad got in mrexcel.com/store lots of different choices books written by me books written by other MVPs lots of good Excel resource out there.
Learn Excel from MrExcel podcast episode 1771.
Count by Month.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in via YouTube, has dates, not just dates, dates and time check this out hundreds of rows of this and needs to create a summary table that shows how many records per month says, "I don't care about the time, don't care about what day of the month, just want to go back to the month." All right, it's not a Friday, it's not a dueling Excel podcast, but I'm still gonna be channeling Mike Excel Is Fun Girvin here because I got this trick from his book.
If we have to do this with a formula, we're going to have to use and IF inside when IF.
So, the first thing we do check to see if the month have all of these records over here, and I put dollar signs in using the F4 key as equal to the month of this cell D2.
If that's true, then we start a brand new IF statement you're saying wait a second.
Why don't we use the AND function because Mike Girvin says, we can't turns out if you're using an array function and this is an array function were we're evaluating a lot of rows in each function and does not work.
I also tried EO month, thinking that would ease it, but no with array functions you're not to do that.
So, first thing we do check to see if the month of all of those is equal to the month of this, then if that's True, see if the year of all of those cells is equal to the year of D2.
if that's True, put a 1 otherwise 0, otherwise 0.
All right! So, that returns inthis case it looks like 374 different results either 1 or 0.
So, we need to wrap those in another function equals SUM we'll do it.
So, add up all the ones and zeros.
Now, can we just press Enter here you see it doesn't work.
You have to hold down Control and Shift and press Enter to make that into an array formula, double click to shoot it down and you're good to go.
That's a confusing formula, it'd be much easier you know I'm guessing this this data's not really going to change it's not like someone's going to come in here and say, oh, no this really happened at February 5th it would be so much easier.
If we could just create a pivot table, 9 mouse clicks in fact.
So, let's do that choose one cell in the data, Insert, Pivot Table, OK and we're going to put date down the left hand side and we're also gonna put date in the value area and Excel is gonna be smart enough to know that we want to count that and then choose the very first date cell, very important we do that under Options we're gonna choose Group Field and choose Months and Years, click OK and we're done.
Now, for each year we get to see by month.
How many records there were.
If you want to see a total by year, well that's kind of a weird bug there I've talked about this before going to Options, Field Settings and turn the Subtotals to Automatic to get the yearly totals to appear as well.
Okay so, your choice either array formula from the book, Control+Shift+Enter or a Pivot Table the array formula will update if something here would change, then you know it will automatically update and I guess that's a good thing, but if your data's not going to change boy, 9 clicks to create a Pivot Table seems to be the way to go.
Oh! Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
So, hey there's a lot of ways to connect with us here at MrExcel.
If you're looking for an Excel book, DVD, e book, something for your Kindle for your iPad got in mrexcel.com/store lots of different choices books written by me books written by other MVPs lots of good Excel resource out there.
