Filtering Dates within the dates and adding corresponding values

[SanjayGMusafir]

Hi Experts

Got stuck up in a strange situation. Tried copying XL2BB from original file - It failed. So copied the actual data to a blank worksheet to explain a situation.

What I need to do -

1. Filter dates based on Financial year (April 1 to March 31 - in India) - May see the blue highlighted rows
2. Add the values in column B out of that filtered data BUT
   1. Take the Value corresponding to date which is Higher B12 (Highlighted Red) for same values in Column C
   2. Include B12 Value and exclude B10 value as A10 is smaller than A12

Tried different ways. But unable to bring it (all) in one formula as I'm making Year over Year, Year End summary.

Please help

Thanks in advance.

Book1
	A	B	C	D
1	12/31/21	34,400	405 FF
2	12/31/21	14,000	979/GH5&7
3	4/30/22	11,000	527/GH5&7
4	6/30/22	13,500	647/GH5&7
5	11/30/22	13,000	1197/GH5&7
6	11/30/22	10,000	256/GH5&7
7	11/30/22	38,200	405 FF
8	11/30/22	15,000	979/GH5&7
9	3/31/23	12,000	527/GH5&7
10	5/31/23	14,580	647/GH5&7
11	7/31/23	10,000	720/GH5&7
12	8/30/23	15,700	647/GH5&7
13	9/29/23	12,800	527/GH5&7
14	10/31/23	14,000	1197/GH5&7
15	10/31/23	10,000	256/GH5&7
16	10/31/23	41,300	405 FF
17	10/31/23	16,500	979/GH5&7
18	8/31/24	11,000	720/GH5&7
19	9/30/24	15,400	1197/GH5&7
20	9/30/24	10,500	256/GH5&7
21	9/30/24	44,600	405 FF
22	9/30/24	16,500	979/GH5&7
23	2/28/25	12,500	647/GH5&7
24	3/31/25	12,000	527/GH5&7
Sheet1

 

[JamesCanale]

Not sure about this one:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
1	12/31/2021	34400	405 FF		FY
2	12/31/2021	14000	979/GH5&7		2021	48400
3	4/30/2022	11000	527/GH5&7		2022	101700
4	6/30/2022	13500	647/GH5&7		2023	120300
5	11/30/2022	13000	1197/GH5&7		2024	122500
6	11/30/2022	10000	256/GH5&7
7	11/30/2022	38200	405 FF
8	11/30/2022	15000	979/GH5&7
9	3/31/2023	12000	527/GH5&7
10	5/31/2023	14580	647/GH5&7
11	7/31/2023	10000	720/GH5&7
12	8/30/2023	15700	647/GH5&7
13	9/29/2023	12800	527/GH5&7
14	10/31/2023	14000	1197/GH5&7
15	10/31/2023	10000	256/GH5&7
16	10/31/2023	41300	405 FF
17	10/31/2023	16500	979/GH5&7
18	8/31/2024	11000	720/GH5&7
19	9/30/2024	15400	1197/GH5&7
20	9/30/2024	10500	256/GH5&7
21	9/30/2024	44600	405 FF
22	9/30/2024	16500	979/GH5&7
23	2/28/2025	12500	647/GH5&7
24	3/31/2025	12000	527/GH5&7
Sheet18

Cell Formulas
Range	Formula
E2:E5	E2	=UNIQUE(YEAR(EOMONTH(+A1:A24,-3)))
F2:F5	F2	=LET(a,FILTER($A$1:$C$24,YEAR(EOMONTH(+$A$1:$A$24,-3))=E2),b,UNIQUE(CHOOSECOLS(a,3)),c,SORT(a,1),d,XLOOKUP(b,CHOOSECOLS(c,3),CHOOSECOLS(c,2),"",0,-1),SUM(d))
Dynamic array formulas.

 

[SanjayGMusafir]

Thanks @JamesCanale This is the exact result I want. Only challenge is ChooseCols function is not available in my Excel 2021 (Mac)

Is there a way to bypass that?

Thanks again

 

[JamesCanale]

I'm not savvy on 2021 - try this:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G
1	12/31/2021	34400	405 FF		FY
2	12/31/2021	14000	979/GH5&7		2021	48400	48400
3	4/30/2022	11000	527/GH5&7		2022	101700	101700
4	6/30/2022	13500	647/GH5&7		2023	120300	120300
5	11/30/2022	13000	1197/GH5&7		2024	122500	122500
6	11/30/2022	10000	256/GH5&7
7	11/30/2022	38200	405 FF
8	11/30/2022	15000	979/GH5&7
9	3/31/2023	12000	527/GH5&7
10	5/31/2023	14580	647/GH5&7
11	7/31/2023	10000	720/GH5&7
12	8/30/2023	15700	647/GH5&7
13	9/29/2023	12800	527/GH5&7
14	10/31/2023	14000	1197/GH5&7
15	10/31/2023	10000	256/GH5&7
16	10/31/2023	41300	405 FF
17	10/31/2023	16500	979/GH5&7
18	8/31/2024	11000	720/GH5&7
19	9/30/2024	15400	1197/GH5&7
20	9/30/2024	10500	256/GH5&7
21	9/30/2024	44600	405 FF
22	9/30/2024	16500	979/GH5&7
23	2/28/2025	12500	647/GH5&7
24	3/31/2025	12000	527/GH5&7
Sheet18

Cell Formulas
Range	Formula
E2:E5	E2	=UNIQUE(YEAR(EOMONTH(+A1:A24,-3)))
F2:F5	F2	=LET(a,FILTER($A$1:$C$24,YEAR(EOMONTH(+$A$1:$A$24,-3))=E2),b,UNIQUE(CHOOSECOLS(a,3)),c,SORT(a,1),d,XLOOKUP(b,CHOOSECOLS(c,3),CHOOSECOLS(c,2),"",0,-1),SUM(d))
G2:G5	G2	=LET(a,FILTER($A$1:$C$24,YEAR(EOMONTH(+$A$1:$A$24,-3))=E2),b,UNIQUE(INDEX(a,,3)),c,SORT(a,1),d,XLOOKUP(b,INDEX(c,,3),INDEX(c,,2),"",0,-1),SUM(d))
Dynamic array formulas.

 

[SanjayGMusafir]

It did the magic. Thanks @JamesCanale