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:
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?
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.
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 Find Column Containing a Value
Sahil
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
Mynda Treacy
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
Melanie Fox
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
Mynda Treacy
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
Mithil
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.
Mynda Treacy
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
Geetha
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?
Mynda Treacy
Hi Geetha,
I’d use a PivotTable and the build in grouping tools.
Mynda
Amir
Great solution and explanation, thanks so much
Philip Treacy
You’re welcome.
Jonathan Contant
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.
Catalin Bombea
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
Tris
I just wanted to say that I found this really helpful. Thank you!
Mynda Treacy
Glad we could help, Tris 🙂
Mangal Jain
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.
Catalin Bombea
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
Ricky
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.
Catalin Bombea
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.
Ricky
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.
Catalin Bombea
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)
Camila
Thank you so much! Your help is really appreciated.
Rana Salame
I love your formula thank you it is saving me so much time
Mynda Treacy
Thanks, Rana. Glad I could help.
Mynda
Michael
=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!
Mynda Treacy
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
Rajesh Sinha
Both are quite useful examples,, plz post more on Data analysis.
Sergey
Wow!
It saved me huge amount of monkey job time )
Thank you!
G D
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.
Mynda Treacy
Glad we could help, GD 🙂
Chris Payne
This is great, thanks very much!
Raeann
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
Mynda Treacy
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
Juan Aguero
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!!
Mynda Treacy
🙂 Wow, thanks Juan. Glad you found it useful.
Mynda
Juan Aguero
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!!
Mynda Treacy
🙂 nice!
Kevin
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).
Mynda Treacy
Nice tip. Cheers, Kevin 🙂
Louisa S. Mcgowan
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.
Carlo Estopia
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
Stephanie Espinoza
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?
Mynda Treacy
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.
Meni Porat
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
Mynda Treacy
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.
Babu
This was a very informative one for me.
Thank you very much.
Mynda Treacy
Thanks, Babu. Glad you liked it 🙂
Vishal
Thanks Minda,
Very good tutorial. Actually I was looking for the same trick..
Thanks Again..
Mynda Treacy
Thanks, Vishal 🙂 Glad I could help.
Yvonne Duarte
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
Mynda Treacy
Hi Yvonne,
Yes, but that would spoil the formula fun 🙂
Cheers,
Mynda.