Find column by header AND filter by another column?

[Better_at_board_games]

I'm working on a table that references another and returns the number of employees in each of several regions, by year. The source table includes a row for each employee, with a value of '1' in the column for each year they worked:

Destination Table:

Year	Region	Employees
2020	a	1
2020	b	2
2021	a	2
2021	b	1

Source Table:

Employee	Region	2020	2021	2022	2023
111	a	0	1	1	1
222	a	1	1	0	0
333	b	1	1	1	1
444	b	1	0	0	0

I'm trying to find a formula to fill the Employees column of the Destination table. How can I search for the correct header in the source table and return the count in that column, after filtering for the correct year?

My first thought was to FILTER() the Source table by the region in column B, but I can't seem to figure out a way to return the correct column based on the year in column A.

I also tried nesting INDEX() and MATCH() to return the correct column for the year, but then I can't run a FILTER().

Is there a way to do this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Employee	Region	2020	2021	2022	2023		Year	Region	Employees
2	111	a	0	1	1	1		2020	a	1
3	222	a	1	1	0	0		2020	b	2
4	333	b	1	1	1	1		2021	a	2
5	444	b	1	0	0	0		2021	b	1
Sheet6

Cell Formulas
Range	Formula
J2:J5	J2	=SUMIFS(INDEX(C:F,,XMATCH(H2,$C$1:$F$1)),B:B,I2)

 

[Better_at_board_games]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Employee	Region	2020	2021	2022	2023		Year	Region	Employees
2	111	a	0	1	1	1		2020	a	1
3	222	a	1	1	0	0		2020	b	2
4	333	b	1	1	1	1		2021	a	2
5	444	b	1	0	0	0		2021	b	1
Sheet6

Cell Formulas
Range	Formula
J2:J5	J2	=SUMIFS(INDEX(C:F,,XMATCH(H2,$C$1:$F$1)),B:B,I2)

My hero! It works perfectly. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.