Subtotal by Month

Learn Excel from MrExcel Podcast, Episode 2169: Subtotal Daily Dates by Month.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well, I was down at Fort Myers, Florida, and Megan had a problem. She said that she has data with daily dates, and wants to add Subtotals, but wants to add those Subtotals by month. Alright, now I have two different ways to do this.

My first way is to insert a new column, insert a new column over here like this, and we'll call it the Month Column. And we're going to use a formula here using the TEXT function. So we take the text of that daily date and we format it to show just a month. So, in quotes, I'm going to do "MMMM YYYY". Like that, that's the custom number format to convert that daily date to a month. Now, see, the beautiful thing is, as I copy that down, all of these dates in January are going to say January 2017.

Now, you can change this format by changing the second part of the TEXT function. I have some examples here. I have some various things you can type in. So I'll... Today's date, various things you can add for the second argument-- what it's going to look like, and then how it's going to appear when we add it to the Subtotals function. Alright. So we had those Totals in all the way down and then we say Data, Subtotal, At each change in Month, Use the Sum function on the Amount field, and click OK. And then what we have when we go to the Number 2 view, is we have Totals for each of the month. That's beautiful.

Now, what if you don't want that month column sitting out there? What we can do here, is take all the items in Column B and-- same trick we used yesterday-- Alt+; to select those cells, and the formula is just going to be = the cells to the left of us. Now to get that formula to enter in all of the cells of the selection, we can then do Ctrl+Enter. Alright. And then you can hide Column A or just leave it out of the Print Range, or whatever you need to do, and you will have your dates going down Column B and then the Totals at the end of each group. Alright, so that's the first method.

The second method-- and this is a little bit sneaky, but I'm happy that it works-- we're going to choose all of these dates here in Column A, and go into Home, and try and find a good date format that shows us just the Month. And there's not one there, so we go into More Number Formats, and even here the ones that show months just show the two-digit month, which I'm not a fan of. Is that saying it's January the 17th or January 2017? So I start with one of these and then go into Custom, and get rid of everything other than the Months, and change the year to be two years, like that. Alright, I don't even want a dash, let's just space like that. Click OK.

Alright, now this was curious to me, I didn't know how this was going to work because these all say January 2017, but they're all different values. What's the Subtotal command going to do, is it going to subtotal at each change in how it looks, or what's actually stored there? Data, Subtotal, At each change in Date, use the Sum function, Added to Amount, and amazingly, it adds the Totals exactly where we want them to be. And then, choose all of Column A, Home, and go back to a short date like that-- and you have daily dates, but monthly totals. It's beautiful.

Alright, this book, 617 Excel mysteries solved, all these kinds of things. I went to a seminar, someone asked me a question and we discovered some cool way to do that, it ends up in the book. That's why in every Edition to the book we end up with more and more and more mysteries solved.

Alright, topics in this Episode for Megan in Fort Myers: How to add Subtotals by Month, when our dates are stored as daily dates. Two methods: Add a new column to the left of date that has the Month it formats that date as a month; add Subtotals by that new column; and then to move the monthly Subtotals back into the date column, collapse the #2 view; select the blanks in the date column; Alt+Semicolon; select the visible cells; and then -Left Arrow Ctrl+Enter, BAM! We've moved it back; go back to #3 view. Method number two; temporarily change the number format in the Date column to show only the Month; add the Subtotals by Date; amazingly you'll get one Total per Month; and then change the number format back to a short date and it looks like you've done the impossible.

Well, hey, I want to thank Megan for showing up in my seminar in Fort Myers, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.