Excel function to dynamically SUM UP data based on matching rows and columns

Question

I have a table with metrics shown as rows and months shown as columns. An example is below:

Quarter	2022-01-01	2022-01-01	2022-01-01	2022-04-01	2022-04-01	2022-04-01	2022-07-01	2022-07-01	2022-07-01	2022-10-01	2022-10-01	2022-10-01
Month	2022-01-01	2022-02-01	2022-03-01	2022-04-01	2022-05-01	2022-06-01	2022-07-01	2022-08-01	2022-09-01	2022-10-01	2022-11-01	2022-12-01
Metrics	Jan 2022	Feb 2022	Mar 2022	Apr 2022	May 2022	Jun 2022	Jul 2022	Aug 2022	Sep 2022	Oct 2022	Nov 2022	Dec 2022
Revenue	1000	1000	1000	500	500	500	100	100	100	0	0	0
Cost	10	10	10	10	10	10	20	20	20	0	5	10

A dynamic summary table of the quarterly data is what I'm after. I can use this function in sumifs to look up the quarter month: SUMIFS([Value row range],[Quarter range],[Quarter wanted]) ([Value row range],[Quarter range],[Quarter wanted])

I still need to manually choose the right value row range to total, though. Is it possible to choose the complete table and then match the right row using labels that match (in this case, a metric)?

Insert Report Month	Dec-22
Last 3 quarter report
Metrics	Q2 2022	Q3 2022	Q4 2022
Revenue	1500	300	0
Cost	30	60	15

I'm aware of the index & match function, but it only looks for the first match and does not sum up all months in the same quarter.

Answer 1

Excel 365 for MAC should have the BYCOL function,

Given:

• 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.

A11: =Metrics[[#All],[Metrics]]
B11: =LET(x,EDATE(Report_Month,SEQUENCE(,3,-6,3)),TEXT(MONTH(x)/3,"\Q0 ") & YEAR(x))

B12: =BYCOL(XLOOKUP(TEXT(DATE(YEAR(Report_Month),MONTH(Report_Month)-9+SEQUENCE(3,,1,1)+SEQUENCE(,3,0,3),1),"mmm-yy"),Metrics[#Headers],INDEX(Metrics,XMATCH(A12,Metrics[Metrics]),0)),LAMBDA(arr,SUM(arr)))

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

Notes

DATE(YEAR(Report_Month),MONTH(Report_Month)-9+SEQUENCE(3,,1,1)+SEQUENCE(,3,0,3),1)   

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.