Index - Match?

[whynot]

I am trying to calculate the total for the quarters for each year of data. I have data for Jan - Dec for each year need to add jan/feb/mar for 1Q, apr/may/jun for 2Q and so on.
i have included the data and how i am trying to set up my worksheet.
Thank you for the help.

New Business - Bankruptcy 1.0.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
8	Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	2004	NA	NA	NA	NA	NA	NA	159,082	192,342	191,244	191,350	211,421	192,407
10	2005	202,217	204,563	201,763	204,009	203,455	209,859	207,622	205,015	184,506	231,096	222,702	232,562
11	2006	216,896	225,947	222,581	223,185	221,944	218,056	219,937	219,163	212,135	207,343	202,446	245,856
12	2007	207,294	217,667	216,139	214,451	220,622	221,664	221,083	218,517	226,413	233,804	213,636	244,205
13	2008	207,604	210,131	220,461	219,286	215,957	210,643	209,790	222,638	210,820	205,724	204,657	186,183
14	2009	207,474	194,915	198,816	198,944	199,968	202,963	207,318	201,742	206,207	203,978	207,895	200,498
15	2010	201,269	202,004	203,545	206,315	210,950	204,740	207,641	207,774	214,216	214,170	221,758	205,425
16	2011	219,956	223,776	213,451	213,884	186,327	219,893	216,774	213,176	212,481	205,716	221,436	228,269
17	2012	185,170	214,138	216,854	217,905	220,401	208,459	208,296	208,916	206,880	212,717	202,237	274,308
18	2013	220,228	211,882	216,620	213,226	206,786	217,010	218,197	221,631	212,617	223,736	221,701	227,846
19	2014	210,241	222,275	228,104	225,967	223,040	226,268	222,001	226,366	228,118	226,970	222,036	205,462
20	2015	256,949	229,473	229,317	231,164	224,660	233,832	237,200	234,449	240,074	243,418	237,012	225,459
21	2016	260,889	241,206	236,843	243,958	254,506	216,326	260,952	248,133	254,858	245,298	252,824	259,738
22	2017	264,310	260,122	262,192	261,436	260,902	267,070	267,812	269,612	260,729	272,776	282,070	267,655
23	2018	297,586	285,342	289,145	285,433	291,926	296,177	292,202	296,131	291,231	286,622	291,106	296,119
24	2019	277,317	298,253	300,259	292,614	285,547	290,506	282,573	292,273	292,756	299,023	293,781	309,607
25	2020	282,802	300,705	257,673	234,838	294,957	377,081	552,748	485,701	437,527	414,335	395,397	347,651
26	2021	481,831	428,386	453239	489588	495976	446039	448197	428523	432170	431427	431017	418884
27	2022	428443	419130	416551	423153	NA	NA	NA	NA	NA	NA	NA	NA
28
29	1Q14
30	2Q14
31	3Q14
32	4Q14
33	1Q15
34	2Q15
35	3Q15
36	4Q15
37	1Q16
38	2Q16
39	3Q16
40	4Q16
41	1Q17
42	2Q17
43	3Q17
44	4Q17
45	1Q18
46	2Q18
47	3Q18
48	4Q18
49	1Q19
50	2Q19
51	3Q19
52	4Q19
53	1Q20
54	2Q20
55	3Q20
56	4Q20
57	1Q21
58	2Q21
59	3Q21
60	4Q21
61	1Q22
SA BA

 

[KRice]

Here is one idea:

MrExcel_20220512B.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
8	Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	2004	NA	NA	NA	NA	NA	NA	159082	192342	191244	191350	211421	192407
10	2005	202217	204563	201763	204009	203455	209859	207622	205015	184506	231096	222702	232562
11	2006	216896	225947	222581	223185	221944	218056	219937	219163	212135	207343	202446	245856
12	2007	207294	217667	216139	214451	220622	221664	221083	218517	226413	233804	213636	244205
13	2008	207604	210131	220461	219286	215957	210643	209790	222638	210820	205724	204657	186183
14	2009	207474	194915	198816	198944	199968	202963	207318	201742	206207	203978	207895	200498
15	2010	201269	202004	203545	206315	210950	204740	207641	207774	214216	214170	221758	205425
16	2011	219956	223776	213451	213884	186327	219893	216774	213176	212481	205716	221436	228269
17	2012	185170	214138	216854	217905	220401	208459	208296	208916	206880	212717	202237	274308
18	2013	220228	211882	216620	213226	206786	217010	218197	221631	212617	223736	221701	227846
19	2014	210241	222275	228104	225967	223040	226268	222001	226366	228118	226970	222036	205462
20	2015	256949	229473	229317	231164	224660	233832	237200	234449	240074	243418	237012	225459
21	2016	260889	241206	236843	243958	254506	216326	260952	248133	254858	245298	252824	259738
22	2017	264310	260122	262192	261436	260902	267070	267812	269612	260729	272776	282070	267655
23	2018	297586	285342	289145	285433	291926	296177	292202	296131	291231	286622	291106	296119
24	2019	277317	298253	300259	292614	285547	290506	282573	292273	292756	299023	293781	309607
25	2020	282802	300705	257673	234838	294957	377081	552748	485701	437527	414335	395397	347651
26	2021	481831	428386	453239	489588	495976	446039	448197	428523	432170	431427	431017	418884
27	2022	428443	419130	416551	423153	NA	NA	NA	NA	NA	NA	NA	NA
28
29	1Q14	660620
30	2Q14	675275
31	3Q14	676485
32	4Q14	654468
33	1Q15	715739
34	2Q15	689656
35	3Q15	711723
36	4Q15	705889
37	1Q16	738938
38	2Q16	714790
39	3Q16	763943
40	4Q16	757860
41	1Q17	786624
42	2Q17	789408
43	3Q17	798153
44	4Q17	822501
45	1Q18	872073
46	2Q18	873536
47	3Q18	879564
48	4Q18	873847
49	1Q19	875829
50	2Q19	868667
51	3Q19	867602
52	4Q19	902411
53	1Q20	841180
54	2Q20	906876
55	3Q20	1475976
56	4Q20	1157383
57	1Q21	1363456
58	2Q21	1431603
59	3Q21	1308890
60	4Q21	1281328
61	1Q22	1264124
Sheet8

Cell Formulas
Range	Formula
B29:B61	B29	=SUM(INDEX($B$9:$M$27,MATCH(2000+RIGHT($A29,2),$A$9:$A$27,0),SEQUENCE(1,3,3*(LEFT($A29,1)-1)+1,1)))

 

[KRice]

Two more options... Unfortunately, when the quarter and year are combined in a text string (such as 4Q21), extra work is needed to extract the 4 and the 21 in order to determine which row and set of columns in the main table need to be summed.

MrExcel_20220512B.xlsx
	A	B	C	D
29	1Q14	660620	660620	660620
30	2Q14	675275	675275	675275
31	3Q14	676485	676485	676485
Sheet8

Cell Formulas
Range	Formula
B29:B31	B29	=SUM(INDEX($B$9:$M$27,MATCH(2000+RIGHT($A29,2),$A$9:$A$27,0),SEQUENCE(1,3,3*(LEFT($A29,1)-1)+1,1)))
C29:C31	C29	=SUM(INDEX(FILTER($B$9:$M$27,RIGHT($A$9:$A$27,2)=RIGHT($A29,2)),,CHOOSE(LEFT($A29,1),{1,2,3},{4,5,6},{7,8,9},{10,11,12})))
D29:D31	D29	=SUM(INDEX(FILTER($B$9:$M$27,RIGHT($A$9:$A$27,2)=RIGHT($A29,2)),,SEQUENCE(1,3,3*(LEFT($A29,1)-1)+1,1)))

 

[whynot]

I used your first suggestion and it worked perfectly.
Are there instances, where it will not work, is that why suggested the other idea.
Thank you very much for your help!!
You saved me a ton of time!!

 

[KRice]

Great!...and an interesting question. The three are similar and should give the same results for data as presented for years in the 21st century and months arranged in order from Jan-Dec. The first takes the last two digits from your summary list leftmost column and adds 2000 to obtain the year, which is then matched to the data table. Should the data table contain 20th century data (e.g., 1999), a match would not be found and an error would be generated. The other two options match the rightmost two digits on the assumption that those years come from the same century, so those formulas would be vulnerable to giving incorrect matches/answers if, say, the data table contained historical data from 1920, 1820, etc. Then there would be no way to distinguish between those and the entry for 2020. I doubt any of these are issues. And all will generate an error if a non-existing/nonsense quarter number (e.g., 5) or year (e.g. 2003) is used in the summary list, as the formulas will not find matches in the data table. There are often multiple ways to produce the same results in Excel, but some formulas are more efficient or easier to understand/maintain.

 

[whynot]

Thank you very much for the explanation. If I do not need to go back to say 1920's i will obviously have to figure out a different formula or method. However, the formula you provided does exactly what i need. The explanation helped me understand the logic. Although, i could have never figured out the formula myself. In fact, there are parts of the formula I don't understand. I just have to hope it does not break. But the spreadsheet has several other formulas that are complicated that, someone like yourself with an immense amount of expertise in excel, helped me build.

Once again thank you very much!!

 

[KRice]

You're welcome...I'm happy to help.