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 / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.