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

0 votes

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 by Kithuzzz
• 38,010 points
593 views

1 answer to this question.

0 votes

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

enter image description here

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 =>
enter image description here

  • 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.
answered Jan 21, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
498 views
0 votes
1 answer

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

answered Oct 23, 2022 in Others by narikkadan
• 63,420 points
6,642 views
0 votes
0 answers

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

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

Nov 2, 2022 in Others by Kithuzzz
• 38,010 points
2,081 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,234 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,420 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
696 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,326 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
1,542 views
0 votes
1 answer

Excel Function to Exclude rows based on certain values

One method is to combine the FILTER() ...READ MORE

answered Jan 23, 2023 in Others by narikkadan
• 63,420 points
5,842 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP