Active Member
October 9, 2024
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
VIP
Trusted Members
December 7, 2016
Trusted Members
October 17, 2018
VIP
Trusted Members
December 7, 2016
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
1 Guest(s)