MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Subtotal by Month


November 07, 2017 - by Bill Jelen

Subtotal by Month

You have daily dates, but you want subtotals by month in Excel. There is an amazingly easy way to do this.

Watch Video

  • Megan in Fort Myers Florida: How to add subtotals by Month?
  • Add a new column to the left of Date
  • Formula is =TEXT(B2,"MMMM YYYY")
  • Add Subtotals by the new column
  • To move the Subtotals back to the Data column:
  • Collapse to #2 view, Select blanks in date column, Alt + ; to select visible cells
  • = Left Arrow Ctrl + Enter
  • Go back to #3 View
  • Method #2
  • Temporarily change number format for dates to show only month
  • Add Subtotals by Date - you will get one total per month
  • Change number format back to short date

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2169 has subtotal Daily dates by
  • month hey welcome back to the MrExcel
  • NetCast I'm Bill Jelen down at Fort
  • Myers Florida and Megan had a problem
  • she said that she has data with daily
  • dates and once to add subtotals but
  • wants to have those subtotals by month
  • all right now I have two different ways
  • to do this on 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 gonna 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 mm mm space why
  • why why why like that that's the custom
  • number format to convert that daily date
  • to a month to seeing the beautiful thing
  • is is I copy that down all of these
  • dates in January are SI 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 all
  • 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 all right so we had those
  • totals in all the way down and then we
  • say data subtotal and each change in
  • month used the sum function on the
  • amount field and click OK and then we
  • have when we go to the number to 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
  • and what we can do here is take all the
  • items in column B and same trick we use
  • yesterday alt semicolon to select those
  • cells and the firm is just going to be
  • equal the cell 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 right 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 all right 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
  • of January 2017 so I start with one of
  • these and then go into custom and get
  • rid of everything other than the months
  • and changed a year to be two years like
  • that all right I don't wanna - let's
  • just space like that click OK
  • alright now this was curious to me I
  • didn't know how this was gonna work
  • because these all say January 2017 but
  • they're all different values what's the
  • subtotal command gonna do is it gonna
  • subtotal at each change in how it looks
  • or what's actually stored their data
  • subtotal at each change in date use or I
  • uses some 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 - holes it's beautiful
  • alright this book 617 Excel mystery
  • 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
  • I'm every addition to the book we end up
  • with more and more and more mystery
  • solved
  • all right topics in this episode for
  • Megan and fort myers how to add
  • subtotals by month when our dates are
  • stored as daily dates to 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 collapsed the
  • number two of you select the blanks in
  • the date column alt semicolon select the
  • visible cells and then equal sign left
  • arrow control enter bam we've moved it
  • back go back to number three view method
  • number two temporarily changed 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
  • well hey I was like Megan for showing up
  • in my seminar in fort myers and i won
  • thank you for stopping by will see you
  • next time for another net cast from mr.
  • excel

Download File

Download the sample file here: Podcast2169.xlsm

Title Photo: Hermann / pixabay


Bill Jelen is the author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.