SUMIFS using multiple criteria

[harriet60]

I couldn't get my previous request to work am looking for a workaround.
Is it possible to SUMIFS using both week and year as a criteria?
ie: SUMIFS (if weeks falls on or between those selected from drop down menu and year falls on or between those selected from drop down menu)
** Return data from week 2, 2021-2022 to week 51, 2021-2022 OR from week 16, 2021 to week 5, 2022-23

Have attached an example (am working across tabs)

Thanks in advance for any help anyone can give

 

[ExceLoki]

created helper lists to make the validation cleaner

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	week from	2	year	2021-2022		academic year	week	name	score	result		years	weeks
2	week to	3	year	2022-2023		2021-2022	2	jack	2	3		2	2021-2022
3						2021-2022	2	jo	6	0		3	2022-2023
4						2021-2022	3	jack	4	2		4
5	name	score	result			2021-2022	4	jo	1	2
6	jack	8	5			2022-2023	2	jack	2	0
7	jo	13	3			2022-2023	2	jo	4	1
8						2022-2023	4	jack	0	5
9						2022-2023	3	jo	3	2
Sheet1

Cell Formulas
Range	Formula
L2:L4	L2	=UNIQUE(week)
M2:M3	M2	=UNIQUE(year)
B6:B7	B6	=SUMIFS(I:I,H:H,A6,G:G,">="&$B$1,G:G,"<="&$B$2,F:F,">="&$D$1,F:F,"<="&$D$2)
C6:C7	C6	=SUMIFS(J:J,H:H,A6,G:G,">="&$B$1,G:G,"<="&$B$2,F:F,">="&$D$1,F:F,"<="&$D$2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
week	=Sheet1!$G$2:$G$9	L2, B6:C7
year	=Sheet1!$F$2:$F$9	M2, B6:C7

Cells with Data Validation
Cell	Allow	Criteria
B1:B2	List	=$L$2:$L$4
D1:D2	List	=$M$2:$M$3

 

[RoryA]

I think you'd need some helper columns to make this work properly with sumifs because you are essentially wanting text to be treated as dates. If you concatenated the week number (formatted to always show 2 digits) onto the end of the year, then you could use >= and <= criteria.