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.
Download the Excel workbook. Note: This is a .xlsx file, please ensure your browser doesn’t change the file extension when you download it.
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.
Rana Salame says
I love your formula thank you it is saving me so much time
Mynda Treacy says
Thanks, Rana. Glad I could help.
Mynda
Michael says
=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 says
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 says
Both are quite useful examples,, plz post more on Data analysis.
Sergey says
Wow!
It saved me huge amount of monkey job time )
Thank you!
G D says
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 says
Glad we could help, GD 🙂
Chris Payne says
This is great, thanks very much!
Raeann says
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 says
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 says
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 says
🙂 Wow, thanks Juan. Glad you found it useful.
Mynda
Juan Aguero says
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 says
🙂 nice!
Kevin says
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 says
Nice tip. Cheers, Kevin 🙂
Louisa S. Mcgowan says
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 says
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 says
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 says
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 says
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 says
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 says
This was a very informative one for me.
Thank you very much.
Mynda Treacy says
Thanks, Babu. Glad you liked it 🙂
Vishal says
Thanks Minda,
Very good tutorial. Actually I was looking for the same trick..
Thanks Again..
Mynda Treacy says
Thanks, Vishal 🙂 Glad I could help.
Yvonne Duarte says
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 says
Hi Yvonne,
Yes, but that would spoil the formula fun 🙂
Cheers,
Mynda.