Excel 365 for MAC should have the BYCOL function,
- Your data table is a Table named Metrics
- Report_Month is a Named Range containing a "real date" in the month of the final month of the desired quarter.
The following formula will return your output and will adjust as you add columns to the data table.
B11: =LET(x,EDATE(Report_Month,SEQUENCE(,3,-6,3)),TEXT(MONTH(x)/3,"\Q0 ") & YEAR(x))
Select B12 and fill down as far as needed.
As written, this does not handle overlapping years, but could be modified to do so if the basic formula will work on your system
creates a matrix of the previous nine month starting dates with each column consisting of a given quarter:
So for 12/1/2022 =>
- The TEXT function then formats the same as the column headers in the Metrics table.
- XLOOKUP will then return the appropriate columns from the table into that matrix, and using the BYCOL allows us to SUM by column which is the relevant quarter.