This post provides a solution for quickly creating formulas to sum monthly data into quarters and full year totals using a free add-in.
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.
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 Quarter Formulas Add-in creates the correct formulas for the quarters and full year in just a few clicks. And it's FREE!
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!
- 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.
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
- How to Install an Add-in
- How to Setup the Quick Access Toolbar (QAT)
- How to Use the QAT for Keyboard Shortcuts
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.
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.
Creating the Formulas
- Click the Quarter Formulas Button.
- 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.
- 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.
- 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.