fbpx
Search
Close this search box.

Quarterly totals when you have multi-year data [SUMPRODUCT again]

Share

Facebook
Twitter
LinkedIn

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:

Quarterly totals when you have multi-year data

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.

Quarterly Totals from Monthly Data - Excel Pivot Report

  1. Make a pivot table with your multi-year data
  2. Now add months to either “rows” or “columns” area.
  3. Sales to the “values” area.
  4. Right click on row and select “Group”
  5. Select “Quarters” and “Years” [learn more about grouping dates in pivot reports]
  6. 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:

Quarterly Totals from Multi-year Monthly Data in Excel

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

5 Responses to “Quarterly totals when you have multi-year data [SUMPRODUCT again]”

  1. Jeff Weir says:

    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.

  2. Mike George says:

    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.

  3. [...] His solution to this is a pivot table, of course, accompanied by a modified SUMPRODUCT formula. [...]

  4. bnergi says:

    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.

  5. Janus says:

    It's great, many thanks

Leave a Reply