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.

Jan 21, 2023 in Others 604 views

## 1 answer to this question.

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.
• 63,420 points

## How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: \$file_name = htmlentities(\$_POST['file_name']); \$sheet_name = htmlentities(\$_POST['sheet_name']); \$number_of_columns = htmlentities(\$_POST['number_of_columns']); \$number_of_rows ...READ MORE

## Merge rows based on value (pandas to excel - xlsxwriter)

I'm attempting to use xlsxwriter to output ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE