Quarter Sum Formulas Add-in - Excel Campus
20

Quarter Sum Formulas Add-in

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 20 comments
Nancy - July 17, 2017

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?

Reply
MD - July 5, 2017

Hi. Are we able to pull data from other worksheets or workbooks using the add in?

Reply
Michael - March 21, 2017

Hi Jon,

Is there a way to change the Quarters in Pivot Tables/Charts to a Fiscal year from a calendar years?

Reply
    Jon Acampora - March 29, 2017

    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!

    Reply
JR - February 20, 2017

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.

Reply
Muhaamad Attia - January 22, 2016

Hi Jon,

Many Thanks for your efforts,I believe that site will benefit for me.

Reply
NATARAJTN - March 14, 2015

Very Very Good site. lot off i got !!

Reply
Sonny - July 22, 2014

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.

Reply
    Jon Acampora - July 22, 2014

    Hi Sonny,
    Are you getting the error when unzipping the file? Do you know what version of Windows and Excel you are using?

    Reply
Ohm - February 2, 2014

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.

Reply
Roy - December 17, 2013

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

Reply
Kendra - October 2, 2013

Thank you so much !!! You have saved me hours of work!

Reply
sonu - July 31, 2013

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

Reply
    Jon Acampora - July 31, 2013

    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

    Reply
don hahn - July 8, 2013

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

Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x