• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Formulas to Summarise Monthly Data into Quarters

You are here: Home / Excel Formulas / Excel Formulas to Summarise Monthly Data into Quarters
Excel Formulas to Summarise Monthly Data into Quarters
June 20, 2013 by Mynda Treacy

I had a question from Somnath this week asking what formula he could use to summarise his monthly data into quarters without having to edit the formula for each quarter.

Letโ€™s imagine Somnath is selling โ€˜thingsโ€™ and his data looks like this:

Excel formula to summarise mths into qtrs

And in rows 16:19 he wants to summarise it into quarters by entering a formula in cell B16 and then copying it to the remainder of the table without so much as an F2, or double click to edit any formulas.

Summarise Monthly Data into Quarters

We can achieve this with a dynamic range using the OFFSET function.

If you havenโ€™t come across OFFSET before I recommend you read up on it here with my treasure map example before continuing on.

Ok, letโ€™s take a closer look at the formula in B16:

=SUM(OFFSET(B$2,3*ROWS(B$2:B2)-3,,3))

Remember, the OFFSET function returns a reference to a range that is a given number of rows and columns from a reference. The syntax is:

=OFFSET(reference,rows,cols,[height],[width])

Iโ€™ve crossed out the cols and width arguments as we donโ€™t need them since our range is only 1 column wide.

In my formula the reference argument is the first cell in the table; B2.

The rows argument tells Excel the number of rows, up or down, that we need to move away from the reference to get to the first cell in our range.

Now remember we donโ€™t want to edit this formula for each quarter so weโ€™ve made it calculate the rows argument dynamically incorporating the ROWS function and the strategic use of absolute and relative references.

Now, the ROWS function simply returns the number of rows in a range. Since there is one row in the range B2:B2 our formula evaluates like this:

=SUM(OFFSET(B$2,3*1-3,,3)) which becomes:
=SUM(OFFSET(B$2,0,,3))

In English the above OFFSET formula reads: starting in cell B2 move zero rows down (i.e. start in cell B2) and make the range 3 cells high, which is:

=SUM(B$2:B4)

Letโ€™s look at Quarter 2โ€™s formula in cell B17:

=SUM(OFFSET(B$2,3*ROWS(B$2:B3)-3,,3))

Notice how the ROWS formula reference is now B2:B3.

So, the ROWS formula evaluates to 2 since thereโ€™s 2 rows in the range B2:B3:

=SUM(OFFSET(B$2,3*2-3,,3)) which becomes:
=SUM(OFFSET(B$2,3,,3))

In English the above OFFSET formula reads: starting in cell B2 move 3 rows down, thatโ€™s B5 and make the range 3 cells high. Which is:

=SUM(B5:B7)

Horizontal Data

Got your months going across columns instead of down rows like the table below?

Excel formula to summarise mths into qtrs

No problem, you can use this formula which uses the COLUMNS function instead of ROWS:

=SUM(OFFSET($L2,,3*COLUMNS($L$2:L2)-3,1,3))

The COLUMNS function returns the number of columns in a range.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Want More?

Check out our Excel Formulas list of free tutorials.

Thanks

Iโ€™d like to thank Somnath for asking this question.

If you liked this please share it with your friends and colleagues using the Google +1, LinkedIn, Facebook and Twitter buttons below.

Excel Formulas to Summarise Monthly Data into Quarters

More Offset Function Posts

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you donโ€™t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy theyโ€™re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: offset function
Previous Post:Excel Find Column Containing a ValueExcel Find Column Containing a Value
Next Post:Excel Bank Reconciliation FormulaExcel Bank Reconciliation Formula

Reader Interactions

Comments

  1. Sahil

    November 23, 2020 at 11:09 pm

    awesome amazing , i was struggling to find a solution of converting Monthly data to Quarterly and you make my life easy
    what is the criterion to decide 3 in row function formulae ie 3*row()-3

    Reply
    • Mynda Treacy

      November 24, 2020 at 8:51 am

      Glad it was helpful, Sahil! I’m not sure what you mean by ‘decide 3 in row’. Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum

      Reply
  2. Melanie Fox

    May 28, 2020 at 1:44 pm

    Firstly the Summarise Monthly Data into Quarters – Horizontal Data instructions are awesome and worked perfectly, thank you so much.

    I have a question, for my data set I have set up quarter columns for two years worth of data. I generate a monthly report so sometimes there isn’t data for all three months in the quarter. Is there a way to set up an additional column or even a cell that will identify what quarters only have partial months data and what months have data?

    Thank you

    Reply
    • Mynda Treacy

      May 28, 2020 at 3:39 pm

      Hi Melanie, I’m glad you found the formula helpful. Yes, you can detect if a period doesn’t have 3 months of data in a quarter. The formula will depend on how your data is laid out and what you hope to then do with that information. Please post your question and sample Excel file with an example of the result you want on our forum and we can help you further.

      Mynda

      Reply
  3. Mithil

    April 29, 2020 at 9:41 pm

    This is interesting. Suppose if I don’t want to use Sum function and I just want to extract the value from the row, how can one use this formula? Just one thing to note, the value that needs to be extracted is from time cards, so in some cases there will be five rows for Monday-Friday and in some Monday to Thursday or whatever days they worked. Long story short the values that need to be extracted are not at regular intervals i.e. every 5 lines or 4 lines.

    Reply
    • Mynda Treacy

      April 30, 2020 at 12:00 pm

      Hi Mithil,

      Please post your question and sample Excel file containing a before and after example on our Excel Forum so we can help you further.

      Mynda

      Reply
  4. Geetha

    March 5, 2020 at 9:56 am

    i have an excel where the months are Dec 2019 thru Feb 2020. How do I make sure my formula totals this as two quarters?

    Reply
    • Mynda Treacy

      March 5, 2020 at 3:48 pm

      Hi Geetha,

      I’d use a PivotTable and the build in grouping tools.

      Mynda

      Reply
  5. Amir

    December 18, 2019 at 7:32 pm

    Great solution and explanation, thanks so much

    Reply
    • Philip Treacy

      December 19, 2019 at 8:02 am

      You’re welcome.

      Reply
  6. Jonathan Contant

    March 5, 2019 at 3:33 am

    These formula look useful but I can’t get it to work in my case. I need to aggregate monthly data in to quarters. I also need the formula to understand that quarters are Jan-Mar, Apr-June, Jul-Sep, and Oct-Dec.

    My data is laid out like this:

    Months (B1:P1) – Cell P1 references user input for reporting month from a different sheet.
    Data (B3:P133) – each row has a different indicator)

    I tried =SUM(OFFSET($B2,,3*COLUMNS($B$2:B2)-3,1,3)) but it of course does not care about the month data.

    I also tried to use Catalin’s formula =SUM(OFFSET($A$2:$C$2,0,MONTH(DATEVALUE(โ€œ01 โ€œ&Sheet1!A1&โ€ 2017โ€ณ))-1)) but was unable to produce the result I am looking for. Any ideas?

    I can easily get the formulas to add three consecutive months but that doesn’t account for the shift in months based on the referenced month and then they are no longer quarters. This report is updated every month.

    Reply
    • Catalin Bombea

      March 6, 2019 at 12:28 am

      Hi Jonathan,
      Can you please open a new topic on our forum? You can upload a sample file so we can see and work on your exact structure.
      Cheers,
      Catalin

      Reply
  7. Tris

    November 8, 2018 at 6:54 am

    I just wanted to say that I found this really helpful. Thank you!

    Reply
    • Mynda Treacy

      November 8, 2018 at 7:41 am

      Glad we could help, Tris ๐Ÿ™‚

      Reply
  8. Mangal Jain

    May 16, 2018 at 9:58 pm

    i want to know how to calculate my monthly purchases, from an yearly purchase sheet, when there are many purchases in a single month. please help.

    Reply
    • Catalin Bombea

      May 17, 2018 at 2:53 am

      Hi Mangal,
      You will have to provide an example of your data, use our forum to upload a sample file. (create a new topic after sign-up). There can be too many solutions, depending on your data structure.
      Catalin

      Reply
  9. Ricky

    December 13, 2017 at 4:53 pm

    How do I use the sum offset formula to add any 3 months of the year depending on the month I pick from the drop down menu which is in another tab?

    Thanks.

    Reply
    • Catalin Bombea

      December 13, 2017 at 9:56 pm

      Hi Ricky,
      This formula:
      =SUM(OFFSET($A$2:$C$2,0,MONTH(DATEVALUE(“01 “&Sheet1!A1&” 2017″))-1))
      will offset the range A2:C2 depending on your selection from Sheet1!a1 (assuming that is the dropdown cell). If you select January, the range will be the same as the initial range, first 3 columns (MONTH number-1 will be 0). If February is selected, the range to sum will be offset by 1 column , Feb-Mar-April data will be added. Same result can be obtained with another similar formula:
      =SUM(OFFSET($A$2:$C$2,0,INDEX({0,1,2,3,4,5,6,7,8,9,10,11},MATCH(Sheet1!A1,{“January”;”February”;”March”;”April”;”May”;”June”;”July”;”August”;”September”;”October”;”November”;”December”},0))))
      Note that if november or december is selected, there should not be 3 cells to add, only 2 or 1. In this case, you should use the “Width” argument of the OFFSET function to shrink the initial range to 2 or 1 column.

      Reply
      • Ricky

        December 13, 2017 at 11:14 pm

        Thank you. Let me try that. This should work for months going across columns right? Iโ€™m adding the PREVIOUS 3 months so November and December should not be a problem. I start with March as the first month in Source from data validation table.
        Thanks.

        Reply
        • Catalin Bombea

          December 14, 2017 at 2:05 pm

          Yes, months should be in columns. If you need more help on this subject, a sample file will help us understand your exact situation. You can upload it on our forum (create a new topic after sign-up)

          Reply
  10. Camila

    September 16, 2017 at 12:08 am

    Thank you so much! Your help is really appreciated.

    Reply
  11. Rana Salame

    July 12, 2017 at 12:07 am

    I love your formula thank you it is saving me so much time

    Reply
    • Mynda Treacy

      July 12, 2017 at 10:34 am

      Thanks, Rana. Glad I could help.

      Mynda

      Reply
  12. Michael

    June 23, 2017 at 12:04 am

    =SUM(OFFSET($L2,,3*COLUMNS($L$2:L2)-3,1,3))
    Hi great stuff, but I guess I’m one of those ‘one percenters’ who need a little extra help…
    Your ‘in plain English’ explanation of the vertical formula was great I ‘almost’ got it.
    BUT no ‘plain English’ for the horizontal which is the one I actually need!!!!
    So ok
    SUM – yes I get it – summing something
    OFFSET – yup – moving ranges of the formula to the appropriate range
    (I’m creating quarterly data from monthly data, monthly on one tab and quarterly on another)
    $L2 – don’t quite understand the ‘absolute reference’ to the column since columns changing across quarters. I DO get that this is the ‘reference’ cell. guess the formula always needs to have the โ€˜reference cellโ€™ in it?
    ,, – Do not understand why there are two comma marks?
    3*COLUMNS – not sure what that means. Does that mean there are 3 columns? and why multiplied?
    $L$2:L2 – really lost on exactly what this means, maybe again must have the โ€˜reference cellโ€™ which is the beginning of whatever the formula is and wherever it goes on the spreadsheet, starting point always there
    -3,1,3 – have no idea what the negative 3 means in this formula. assuming 1 is one row high and 3 is 3 columns wide??

    THANK YOU!

    Reply
    • Mynda Treacy

      June 23, 2017 at 10:21 am

      Hi Michael,

      The 3*COLUMNS(…)-3 is because there are 3 months in a quarter, so you want to jump 3 columns across at a time and include 3 columns in your SUM range. The COLUMNS function returns the count of columns in the range specified.

      The $L$2:L2 uses absolute referencing on the first cell reference and not the second so that when you copy the formula to other cells the L2 updates accordingly. e.g. L2 will become M2 and so on as you copy it across the columns.

      The first argument of OFFSET, $L2 is the starting point/reference argument for the OFFSET function.

      The two consecutive commas are placeholders for the arguments in OFFSET that we don’t need to use. More on how OFFSET works here.

      To get a better understanding I recommend you download the workbook and use the Evaluate Formula tool to see how the formulas are evaluating. Try it on cell L9 and then look at cell M9 and M10 to see the differences.

      Mynda

      Reply
  13. Rajesh Sinha

    April 7, 2017 at 3:38 am

    Both are quite useful examples,, plz post more on Data analysis.

    Reply
  14. Sergey

    March 3, 2016 at 6:39 pm

    Wow!
    It saved me huge amount of monkey job time )
    Thank you!

    Reply
  15. G D

    February 9, 2016 at 10:13 pm

    Great help – thank you!! My heart sank when I found another vertical example so very pleased to scroll down and find a horizontal example too.

    Reply
    • Mynda Treacy

      February 10, 2016 at 8:41 am

      Glad we could help, GD ๐Ÿ™‚

      Reply
  16. Chris Payne

    January 30, 2016 at 1:55 am

    This is great, thanks very much!

    Reply
  17. Raeann

    January 14, 2016 at 12:38 am

    Question: what if their are multiple rows with the same month and I want to aggregate them by quarter as well.

    For example in your file there would be three of each month and I still want them to be in quarter file.

    Does this still work?

    Thanks

    Raeann

    Reply
    • Mynda Treacy

      January 14, 2016 at 9:07 am

      Hi Raeann,

      To be honest, I’d use a PivotTable. Just make sure you have a column that contains proper dates, then you can use the Group tool in the PivotTable to automatically summarise them into quarters:

      https://www.myonlinetraininghub.com/excel-pivottable-group-data-video

      Mynda

      Reply
  18. Juan Aguero

    August 6, 2014 at 11:06 am

    Wow, I don’t have words to describe this tutorial, everything I can say is little compared to its power. Excellent, Mynda, you are always pushing the boundaries of Excel!!

    Reply
    • Mynda Treacy

      August 6, 2014 at 11:08 am

      ๐Ÿ™‚ Wow, thanks Juan. Glad you found it useful.

      Mynda

      Reply
      • Juan Aguero

        July 13, 2017 at 10:22 pm

        This formula is truly amazing and impressive, it really shows the power of Excel knowledge, what a great strategic use of functions! Yesterday I sent the workbook example to some co-workers and they were totally impressed with what can be achieved!!

        Reply
        • Mynda Treacy

          July 14, 2017 at 9:17 am

          ๐Ÿ™‚ nice!

          Reply
  19. Kevin

    August 2, 2013 at 11:44 pm

    I like to use the =if(mod(month(cellref),3)=0,sum(cellref:offset(cellref,x,x)),0) function in a rolling dataset and use index/match to pull the data needed in a summary table matching the date reference. You can replace the 3 in the mod function with a 6, 9, 12 to get x months ending totals as well as long as you make sure the offset variable matches the mod variable (3 in this case).

    Reply
    • Mynda Treacy

      August 4, 2013 at 3:40 pm

      Nice tip. Cheers, Kevin ๐Ÿ™‚

      Reply
  20. Louisa S. Mcgowan

    July 3, 2013 at 3:13 am

    which can be read as “if the category at entry C42 to C2000 equals the one at A4…” The $ signs make the formula use absolute references so that when I duplicate the formula, it won’t shift the references. 42 refers to the first row where ledger entries begin. 2000 is an arbitrary large number I am not likely to reach. You can think of $C$42:$C$2000 as being equivalent to something like “C:C” from the first examples. A4 is a reference to a specific category name from the top section.

    Reply
    • Carlo Estopia

      July 3, 2013 at 10:11 am

      Hi Louisa,

      I don’t quite get your problem here.
      Please do clarify if there’s anything
      you need. You may also send some file
      here: HELP DESK.

      Cheers,

      CarloE

      Reply
  21. Stephanie Espinoza

    June 26, 2013 at 1:09 pm

    The formula above returns the count for the entire range of dates but I need to find the number for each month, ie the number for May, June etc. How would that be done?

    Reply
    • Mynda Treacy

      June 26, 2013 at 1:17 pm

      Hi Stephanie,

      I’m not sure I understand your question since the numbers for each month are already summarized in this example. If you have multiple instances of dates for each month that you want to sumamrize there are a few options:

      1. SUMIFS
      2. SUMPRODUCT
      3. PivotTables

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  22. Meni Porat

    June 26, 2013 at 6:28 am

    Hi Mynda,
    I hope you’ll post here my suggestion for a more “realistic” situation in which there is more than one instance per month [using the SUMPRODUCT function].

    BTW, I don’t think that this can be solved with the SUMIFS function, but of course I might be wrong….

    Best Regards,
    Meni Porat

    Reply
    • Mynda Treacy

      June 26, 2013 at 11:27 am

      Hi Meni,

      Thanks for your suggestion. My example is taking data that has already been summarised into a monthly report, but you could summarise the source data into quarters using SUMPRODUCT or SUMIFS.

      SUMIFS allows multiple criteria (in this case start and end dates for each quarter), just as SUMPRODUCT does.

      As with most things, Excel had many ways to ‘skin a cat’ ๐Ÿ™‚

      Kind regards,

      Mynda.

      Reply
  23. Babu

    June 22, 2013 at 3:19 pm

    This was a very informative one for me.

    Thank you very much.

    Reply
    • Mynda Treacy

      June 22, 2013 at 4:34 pm

      Thanks, Babu. Glad you liked it ๐Ÿ™‚

      Reply
  24. Vishal

    June 21, 2013 at 3:00 pm

    Thanks Minda,
    Very good tutorial. Actually I was looking for the same trick..
    Thanks Again..

    Reply
    • Mynda Treacy

      June 21, 2013 at 4:44 pm

      Thanks, Vishal ๐Ÿ™‚ Glad I could help.

      Reply
  25. Yvonne Duarte

    June 21, 2013 at 12:44 pm

    I would group this data using a pivot table. Group the months into Quarters. I do use the OFFSET function frequently – but a pivot table seems easier in this instance

    Reply
    • Mynda Treacy

      June 21, 2013 at 1:02 pm

      Hi Yvonne,

      Yes, but that would spoil the formula fun ๐Ÿ™‚

      Cheers,

      Mynda.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.