Extract number of live receipts yearwise

[Muthukrishnan V]

Sample1.xlsx
	B	C	D	E	F	G	H
1	Excel 365
2	Extract data yearwise
3	Status	Receipt No	Start Date	End Date	Amount	Name	Firm
4	Live	1101	25-03-2023	11-06-2024	10000	KM	ABC
5	Closed			22-07-2025		KM	DEF
6	Live	12035	21-07-2021	31-08-2023	20000	KM	DEF
7	Live	12024	11-06-2021	31-01-2024	15000	KM	ABC
8	Closed		22-07-2021	18-09-2023	30000	VMK	GHI
9	Live	14064	27-03-2020	28-03-2025	25000	VMK	GHI
10	Live	14063	27-03-2020	28-03-2025	25000	KM	GHI
11
12		RESULT		Number of LIVE Receipts
13				with END date falling in the year
14		Name	Firm	2023	2024	2025	2026
15		KM	ABC		2
16		KM	DEF	1
17		KM	GHI			1
18		VMK	ABC
19		VMK	DEF
20		VMK	GHI			1
21
22	Kindly advise me formulas in E14:H19 to extract yearwise live receipts. Thanking you
Sheet1

 

[awoohaw]

try this:

Mr excel questions 48.xlsm
	B	C	D	E	F	G	H
1	Excel 365
2	Extract data yearwise
3	Status	Receipt No	Start Date	End Date	Amount	Name	Firm
4	Live	1101	2023-03-25	2024-06-11	10000	KM	ABC
5	Closed			2025-07-22		KM	DEF
6	Live	12035	2021-07-21	2023-08-31	20000	KM	DEF
7	Live	12024	2021-06-11	2024-01-31	15000	KM	ABC
8	Closed		2021-07-22	2023-09-18	30000	VMK	GHI
9	Live	14064	2020-03-27	2025-03-28	25000	VMK	GHI
10	Live	14063	2020-03-27	2025-03-28	25000	KM	GHI
11
12		RESULT
13				Number of LIVE Receipts with END date falling in the year
14		Name	Firm	2023	2024	2025	2026
15		KM	ABC	0	2	0	0
16		KM	DEF	1	0	0	0
17		KM	GHI	0	0	1	0
18		VMK	ABC	0	0	0	0
19		VMK	DEF	0	0	0	0
20		VMK	GHI	0	0	1	0
Muthukrishnan V

Cell Formulas
Range	Formula
E15:H20	E15	=SUM((--($C15=$G$4:$G$10))*(--($D15=$H$4:$H$10))*(--(E$14=YEAR($E$4:$E$10)))*(--($F$4:$F$10>0)) *(--($B$4:$B$10="Live")) )

 

[Muthukrishnan V]

Thanking you Mr Awoohaw sir. The task is accomplished.
I thank our Forum for the kind help.

 

[awoohaw]

I'm happy you found a solution. I'm sure there are other ways to perform the task as well.

Best Wishes!