Here is a problem we face very frequently. You have a list of values by months. And you want to find out the totals by Quarter. How do you go about it?
There are 2 options:
- You can make a pivot report from the data and then group dates in that to find totals by quarter
- You can write formulas to find the totals by quarter
While option 1 is good for knowing the values, we need option 2 if you want the values to be fed to another report, chart or dashboard.
Writing Formulas to Get Quarterly Totals from Monthly Data:
First, understand a little math formula called ROUNDUP():
ROUNDUP formula takes a number and rounds it up to the nearest fraction as specified by you. So for eg.,
ROUNDUP(1.234,1) = 1.3ROUNDUP(1.234,2) = 1.24ROUNDUP(1.234,0) = 2ROUNDUP(1.0,0) = 1
Now, assuming your monthly data is in cells B4:C15,
Our objective is to find Quarters from dates and then add up all items in Q1 against Quarter 1.
We can get the month from a date using MONTH() formula. If we divide the month by 3 and then round the value up to nearest integer we will get the Quarter.
So, A formula like =ROUNDUP(MONTH(B4)/3,0) should tell us the quarter for the month in the cell B4.
So the final formula for calculating sum of all the sales in Q1 is =SUMPRODUCT((ROUNDUP(MONTH(B4:B15)/3,0)=1)*(C4:C15)).
How this formula works?
Well, the portion ROUNDUP(MONTH(B4:B15)/3,0)=1 gives a bunch of 1 and 0s, one where the month belongs to Q1 and zero where it is not. When you multiply these ones and zeros with actual sales values in C4:C15, you get the total sales in Q1.
Since SUMPRODUCT has magical powers, it just processes all these ranges of data without batting an eyelid.
Download the example worksheet and play with this formula
Go ahead and download the example workbook and play with SUMPRODUCT formula to understand this better.
How do you calculate Quarterly Totals from Monthly Data?
Do you know a better way to do such calculation? How do you usually do it? Please share your tricks and ideas using comments.
