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.

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:

http://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

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.