In yesterdays post – Find Quarterly Totals from Monthly Data, we have learned how to use SUMPRODUCT formula to find totals by Quarter from a set of monthly values. The approach is fine, but has one glaring draw back.
It only works when you have data for one year.
In that post, Martin said,
…
Ever since i read this post, I am struggling with a table that has the same layout as the example, and I wanted to add the totals per year and per Q, years as rows, Qs as columns.
…
So, you want to get the totals like this:
Of course, the easiest and quickest option is to use a pivot report with grouped dates. It takes a few clicks and you have the data.
- Make a pivot table with your multi-year data
- Now add months to either “rows” or “columns” area.
- Sales to the “values” area.
- Right click on row and select “Group”
- Select “Quarters” and “Years” [learn more about grouping dates in pivot reports]
- And your report is ready.
But if prefer a formula version, like Martin does, then,
Assuming you have quarters in a column and years in a row (cells F4:H4), like this:
And monthly data in the range: B4:C39,
The formula will be,
=SUMPRODUCT((ROUNDUP(MONTH($B$4:$B$39)/3,0)=ROWS($E$5:E5))*($C$4:$C$39)*(YEAR($B$4:$B$39)=F$4))
How this formula works?
Today is Friday and I am not in a mood to explain how this formula works. But I will give you some clues,
- The Red portion finds if a month is in Q1 [ROWS($E$5:E5) is used to get running numbers – learn more about this technique]
- The Green portion finds if a month is in the given year
- The Blue portion is the actual values
Now, run to the espresso machine, get a latte and figure out why this works.
Download the example file:
I have enhanced yesterdays file with multi-year data and added both pivot report and formula based quarterly totals in the file. Go ahead and download it. Play with the file to learn how this works.
Share your tips and ideas:
We have excel rockstars like Daniel Ferry telling us some really awesome ways to improve the formula yesterday. I encourage you to share more tips and ideas on how you usually handle situations like these.
5 Responses to “Quarterly totals when you have multi-year data [SUMPRODUCT again]”
I know I've posted this somewhere else before, but it's worth mentioning again...one problem with grouping dates by year in pivottables is that the year is always a calander year. If your organisation has a fiscal year that runs from 1 July, you can still use a pivottable, but you might have to add a 'fiscal year' row to your data source, or put some formulas off to the side of the pivottable somewhere that pull the info into the appropriate groupings. Problem with the formula approach is if your pivottable structure changes (for instance you filter some items out of it), these formulae might not be pointing to the right place (unless you're using a robust GETPIVOTDATA formula).
But a way around this is to use your original pivottable and new formula row as the source of another pivottable.
If you want your dates to coincide with a fiscal year that runs from 1 July, then you could put a new column to the left of your original pivottable (rather than the right, where it would get overwritten if the Master Pivot Table has any more row or data fields added to it) and use the formula to return a financial year field, using something like:
=IF(MONTH(B2)>6,YEAR(B2)+1,YEAR(B12)) …assuming this formula is in A2 and copied down, and the pivottable has dates in row B2, and you put ‘financial year’ as a header in A1
Then add a new pivottable to your sheet that includes the original pivot table as well as row A through to the last row that has pivot table data, and you’ve solved your problem. Probably best to define a dynamic range for this in case the Master pivot table grows or shrinks.
I think sumproduct formulas are far to complicated to bother putting into a spreadsheet unless it is going to be reused or updated, but I loved the method given to calculate the quarter from a given date. But I did want financial quarters and I think this will do it:
=IF(ROUNDUP(MONTH(E2)/3,0)=1,4,ROUNDUP(MONTH(E2)/3,0)-1)
Basically it just makes quarter 4 into quarter 1, and reduces all other quarters by 1.
[...] His solution to this is a pivot table, of course, accompanied by a modified SUMPRODUCT formula. [...]
Hi, this formula helped me a lot but I have still problems. Some cells do not have dates (eg. "shipment dates") but since the shipment have not been done yet, the value ("order") entered for August is calculated in January automatically. How can I ignore empty date cells and take the sum of them in "Pending" cell? Thanks.
It's great, many thanks