Hello,
It would be great if I can get some assistance with a formula. I have a data set which spans many months across many years and I would like to be able to sum monthly values and group by each year and each quarter.
I have been able to get some success with this using the SUMPRODUCT which can get total by years, though i fall short with that in trying to have it work for each quarter, instead it sums all quarter 1, all quarter 2, etc across the multiple years rather than by discreet year.
I am getting a result now using ROUNDUP(month) although I need to recreate the formula for each quarter in my range. Is there a better way this can be written to have it dynamic and work across all years / quarters?
I have attached a sample:
Monthly data in columns D:AM
Totals Year/Quarter in columns AN:BB
Hello,
It seems the sample file didn’t get uploaded.
Br;
Anders
@Anders: this poster has three posts all relating to the same data.
Doesn't say which version he's using but this is the file he attached in one of his posts
Hi @Anders,
Sorry for that. i will try again for the dataset here. Data2 is different to this request.
Hello,
I had lots of issues with the dates in your sample file, so I took the liberty to make a variant of it. You need to check if the numbers are as expected, I think it's correct, but your forecast criteria makes it a bit troublesome for Q1, so it is possible I have missed some part.
Normally I would use a normalised table and present the data in a Pivot Table, but still, your forecast criteria would require some Power Pivot calculations.
I used SUMIFS function to create the forecast summary per quarter, but you need of course to replace my table references so it matches yours, hopefully this helps you forward.
Br,
Anders
