I have a table with metrics shown as rows and month shown as columns. 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 |
I want to have a dynamic summary table of quarterly data. I can use sumifs and look up the quarter month using this function: SUMIFS([Value row range],[Quarter range],[Quarter wanted])
However, i still have to manually select the correct value row range to sum. Is it possible to select the entire table and then match the correct row based on matching labels (metric in this case)?
| 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.
Thanks for helping!
CodePudding user response：
Excel 365 for MAC should have the BYCOL function,
Given:
- Your data table is a
TablenamedMetrics Report_Monthis 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.
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
TEXTfunction then formats the same as the column headers in the Metrics table. XLOOKUPwill then return the appropriate columns from the table into that matrix, and using theBYCOLallows us toSUMby column which is the relevant quarter.