I work for a non-profit that provides healthcare and we have to report monthly and quarterly data on productivity. I have attached a very short sample of a significantly larger spreadsheet (133 rows).
Currently, an end user will type in a reporting period on another sheet that P1 will reference. As you can see, the months then adjust to a preceding month to show historic data. The months range is dynamic based on user input. The end user also selects a facility or "All" and the data then gets pulled for each indicator through index/match based on the users input of date and facility. I have simplified it in the sample to just show the data for one facility.
My current quarterly formula looks at blocks of three months at a time but those are not proper "quarters" (Jan-Mar, Apr-June, Jul-Sep, and Oct-Dec) as you can see entering 7/1/2017 should yield a Q3 '17 of 65, not 172 (sum of 5/1-7/1).
I tried =SUM(OFFSET($B2,,3*COLUMNS($B$2:B2)-3,1,3)) but it of course does not care about the month data.
I also tried =SUM(OFFSET($A$2:$C$2,0,MONTH(DATEVALUE(“01 “&A1&” 2017″))-1)) but was unable to produce the result I am looking for.
Any help is much appreciated.
After a week of reading, searching, and trying, I finally have a solution for anyone else trying to do this:
For Current Q:
=SUMPRODUCT(--(YEAR($B$1:$P$1)=YEAR(EDATE($P$1,0))),--(ROUNDUP(MONTH($B$1:$P$1)/3,0)=ROUNDUP(MONTH(EDATE($P$1,0))/3,0)),--($B3:$P3))
For previous Q:
=SUMPRODUCT(--(YEAR($B$1:$P$1)=YEAR(EDATE($P$1,-3))),--(ROUNDUP(MONTH($B$1:$P$1)/3,0)=ROUNDUP(MONTH(EDATE($P$1,-3))/3,0)),--($B3:$P3))
To go further back, just change the "-3" after each EDATE to a multiple of 3 (e.g.: 3 = Q - 1Q, 6 = Q - 2Q, 9 = Q - 3Q).
Hopefully this will save someone else the time. Big thanks to Hangman and Daddylonglegs on Excel Forum.
Hi Jonathan
Thanks for sharing.
You can also use SUMIFS to get the result you want. From your example, there can be as many as 6 Quarters (you only showed 5 in your example)
My formulas in row 2 can be further modified to include the EDATE if necessary.
Hope this provide another alternative for you.
Cheers
Sunny
