A solution for quickly creating Excel SUM formulas to sum monthly data into quarters and full year totals using a free add-in. Creating quarterly sum formulas is typically a time consuming process because you can not copy/drag the formulas across to produce totals for Q2 to Q4.
This post provides a solution for quickly creating formulas to sum monthly data into quarters and full year totals using a free add-in.

The Problem

When using the SUM function to sum monthly data into quarterly totals, you can NOT create the formula for the 1st quarter and then copy/drag the formula to the right.

Quarter Formulas Incorrect with Drag Right
Click to Enlarge

The cell references will only move one column to the right instead of three columns.  If we copy the formula above for Q1 to the right, the resulting formula will give us the total for Feb-Apr.  The correct formula should be a total for Apr-Jun.

The Solution

The Quarter Formulas Add-in creates the correct formulas for the quarters and full year in just a few clicks.  And it's FREE!

 Excel Quarter Formulas Demo

And it Gets FASTER!

You can add the Quarter Formulas button to the Quick Access Toolbar to launch it with a keyboard shortcut.  Then you will be creating all the formulas in under 2 seconds!

Quarter Formulas Button Keyboard Shortcut Demo

These articles will explain how to setup the Quick Access Toolbar (QAT) and use the QAT for keyboard shortcuts.

Benefits

  • Four quarter and full year SUM formulas created in 2 seconds.
  • No typing or complex formulas needed.
  • Creates easy-to-read SUM formulas.
  • Automatically determines 1st month cell when Q1 cell is selected.

Download

Quarter_Sum_Formulas_Add-in.zip (758.5 KB)

The add-in file and installation guide are included in the zip file.  The add-in is compatible with Excel 2007, 2010, 2013.  Please let me know if you are interested in a version compatible with Excel 2003.

Subscribe to our email newsletter below to stay updated with changes.

Related Help Articles

Instruction Guide

This add-in will save you lots of time when creating quarter and full year formulas, and it is very easy to use.

See the guide on how to install an add-in (included with download).  After the add-in is installed, you will see the Formula Tools menu on the Add-ins tab of the ribbon.

Quarter Formulas Ribbon Screenshot

The Formula Tools button is a drop-down menu that includes a button for the Quarter Formulas function.  More functions will be added to this menu in the future.  See below for instructions on how to setup a keyboard shortcut to launch the function.

Report Structure Requirements

The Quarter Formulas add-in requires all the month data be in 12 consecutive columns.  The four quarter and year total formulas will be created in five consecutive columns.  You will choose the starting cell (Q1) that the formulas will be created in.  The created formulas do NOT have to be next to the monthly data.

Quarter Formulas Report Structure Requirements

Creating the Formulas

  1. Click the Quarter Formulas Button.
  2. Select the 1st Quarter cell where you want the formulas to be created and click OK.
    – Formulas for Q2-Q4 and Full Year sum will be created to the right of this cell.
    – The selection defaults to the active (selected) cell in the worksheet.  Selecting the Q1 cell prior to clicking the Quarter Formulas button will save you a step.
  3. Select the cell that contains month 1 data and click OK.
    – This is the 1st month in the 1st quarter (January if you are on a calendar year).
    – The selection defaults to 12 cells to the left of the Q1 cell selected in the previous step.  If the quarterly total cells are directly to the right of the monthly data then you will not have to select the month 1 cell.  This will save you another step.
  4. The SUM formulas will be created in the Q1 cell and the four cells to the right of it.
    – Copy the formulas down to fill all the rows in your report.

What do you think?

Please leave a comment with any questions or suggestions.

21 comments

Your email address will not be published. Required fields are marked *

  • Add is good but i dont want sum total rather I need quarterly summation of net months (i.e. after initial 12 months). Do I need to re apply macro or there is another easy way out?

    • Hi Michael,

      Unfortunately, we cannot display fiscal quarters with the pivot table Grouping feature. To display fiscal quarters in the pivot table we need to add a column to the source data that contains or calculates the fiscal quarter for each row of data. I explain more about these methods in my article on grouping dates in pivot tables. I will also create an article on how to write a formula to calculate fiscal quarters. Thanks!

  • Thanks for the add-in. I found it very useful. Love this site especially the way you take care not to provide lots of general excel information but only curated most useful ones.

  • Hi Jon,
    I’m unable to open the zip file for some reason after i download it.

    Its giving me an error saying “It doesn’t appear to be a valid archive”
    I got the same error when i tried downloading other add ins you have.
    Can you please fix it. THis add in can be really useful as i’ve lot of quarter end formulas i need to add.

  • This is so useful add-in for me. It’s very simple and effective!!

    One suggestion, if you allow me, is to be able to link monthly data from different sheet. When I tried linking to different sheet, it creates the formular but it links monthly data from its own worksheet.

  • Hi, this is pretty cool, thanks for providing it. I must say it would be more usefull for me if it was possible to do multiple years in one go, without the yearly total in the 5th column. So if it just keeps going untill there’s no more data, for more than 4 quarters.

    Thanks

  • Hi Jon,

    The above addin is not working in below mentioned two caes:

    1) where the monthly data of product is from top to bottom i.e

    example
    Months Product 1 Product 2 Product 3 Product 4 Product 5
    1-Jan-13 300000 150000 150000 100000 150000
    1-Feb-13 300000 150000 150000 100000 150000
    1-Mar-13 250000 100000 300000 250000 150000
    1-Apr-13 150000 150000 100000 150000 100000
    1-May-13 150000 350000 300000 250000 150000
    1-Jun-13 100000 350000 300000 250000 150000
    1-Jul-13 150000 250000 150000 150000 100000
    1-Aug-13 150000 100000 150000 100000 150000
    1-Sep-13 150000 250000 150000 150000 100000
    1-Oct-13 100000 150000 100000 150000 100000
    1-Nov-13 150000 350000 300000 250000 150000
    1-Dec-13 100000 350000 300000 250000 150000

    2) if i would like to do half year/yearly totals,then also this addin will not be useful.

    please confirm if my understanding is correct.

    please also advise some solution for this.

    thanks

    • Hi Sonu,

      You are correct on both points. Currently, the add-in only works to sum monthly data that is laid out horizontally. The vertical sum and half yearly totals could be additional enhancements to the add-in. I will let you know when that functionality is available.

      Thanks,
      Jon

  • very useful. Accountants/Finance are always adding qtrly totals, simple and effective tool especially after adding it to the quick access toolbar.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly