Sumproduct based on drop down criteria

[HawkeyeBC]

I have a spreadsheet that tracks NFL point spread results. I have a bunch of "trends" labeled 1-20 and I want to be able to search the trends and also search a date range. For example, Trend #1 between weeks 1-12 of the NFL season is 24-9-1 and stuff like that. Is something like this possible to create?

Column Y labeled Ulk Cover is the determinant of the W/L/P
The 1-20 columns off to the right are the "trends"

Year

Week

A Cover

A Spread

Away

A Pts

H Pts

Home

H Spread

H Cover

A Spr

A Luck

A %

H Spr

H Luck

H %

Rk Gap

% Gap

ULk Team

7 Pt

14 Pt

Ulk Rec

Cb Rec

Code

ULk Cover

7 PtT Cvr

14 PtT Cvr

Same Div

Number

Combo

Pct

B/S

Pick

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

2023​

1​

Yes

4.5​

Lions

21​

20​

Chiefs

-4.5​

No

Dog

15​

-1.20%​

Fav

5​

8.50%​

10​

9.70%​

Lions

Yes

12-3

15-18

AwayDog

Yes

Yes

No

No

2023​

1​

No

3.5​

Panthers

10​

24​

Falcons

-3.5​

Yes

Dog

11​

3.20%​

Fav

26​

-5.90%​

15​

9.10%​

Falcons

Yes

7-8

14-13

HomeFav

Yes

Yes

Yes

No

2023​

1​

No

-2.5​

Bengals

3​

24​

Browns

2.5​

Yes

Fav

7​

6.60%​

Dog

29​

-8.90%​

22​

15.50%​

Browns

Yes

12-11

18-11

HomeDog

Yes

Yes

Yes

No

2023​

1​

No

-5​

Jaguars

31​

21​

Colts

5​

No

Fav

25​

-5.40%​

Dog

31​

-14.00%​

6​

8.60%​

Colts

Yes

Yes

6-7

14-15

HomeDog

No

Yes

Yes

Yes

1

10-4-0

71.4%

B

Yes

Yes

2023​

1​

Yes

6​

Buccaneers

20​

17​

Vikings

-6​

No

Dog

16​

-1.40%​

Fav

1​

23.70%​

15​

25.10%​

Buccaneers

Yes

4-7

15-15

AwayDog

Yes

No

Yes

No

4

5-9-1

35.7%

S

Yes

Yes

2023​

1​

Yes

3​

Titans

15​

16​

Saints

-3​

No

Dog

19​

-3.20%​

Fav

32​

-18.40%​

13​

15.20%​

Saints

Yes

9-12

9-17

HomeFav

No

Yes

No

No

2023​

1​

Yes

-2.5​

49ers

30​

7​

Steelers

2.5​

No

Fav

18​

-3.10%​

Dog

12​

2.90%​

6​

6.00%​

49ers

Yes

12-9

19-11

AwayFav

Yes

Yes

No

No

2023​

1​

Yes

7​

Cardinals

16​

20​

Commanders

-7​

No

Dog

13​

0.30%​

Fav

10​

3.50%​

3​

3.20%​

Cardinals

Yes

Yes

9-8

15-13

AwayDog

Yes

Yes

Yes

No

3, 8

14-3-0

82.4%

B

Yes

Yes

Yes

2023​

1​

Yes

10​

Texans

9​

25​

Ravens

-10​

No

Dog

28​

-8.00%​

Fav

8​

6.40%​

20​

14.40%​

Texans

Yes

10-7

19-10

AwayDog

No

Yes

Yes

No

10

2-10-0

16.7%

S

Yes

Yes

2023​

1​

Yes

1​

Packers

38​

20​

Bears

-1​

No

Dog

23​

-4.90%​

Fav

30​

-10.20%​

7​

5.30%​

Bears

No

9-11

16-13

HomeFav

No

Yes

Yes

Yes

5, 9, 11

0-1-0

0.0%

S

Yes

Yes

Yes

Yes

2023​

1​

Yes

3.5​

Raiders

17​

16​

Broncos

-3.5​

No

Dog

20​

-3.60%​

Fav

27​

-6.30%​

7​

2.70%​

Broncos

Yes

8-10

16-12

HomeFav

No

Yes

Yes

Yes

11

5-7-0

41.7%

S

Yes

Yes

2023​

1​

No

-3.5​

Eagles

25​

20​

Patriots

3.5​

Yes

Fav

2​

15.90%​

Dog

17​

-2.40%​

15​

18.30%​

Patriots

Yes

Yes

6-16

11-18

HomeDog

No

Yes

Yes

No

6

16-11-0

59.3%

B

Yes

Yes

2023​

1​

Yes

3​

Dolphins

36​

34​

Chargers

-3​

No

Dog

14​

-0.10%​

Fav

9​

4.60%​

5​

4.70%​

Dolphins

Yes

Yes

3-5

14-14

AwayDog

Yes

Yes

Yes

No

3, 8

14-3-0

82.4%

B

Yes

Yes

Yes

2023​

1​

Yes

5.5​

Rams

30​

13​

Seahawks

-5.5​

No

Dog

24​

-5.10%​

Fav

21​

-4.20%​

3​

0.90%​

Rams

Yes

Yes

9-8

20-11

AwayDog

Yes

Yes

Yes

Yes

3, 8

14-3-0

82.4%

B

Yes

Yes

Yes

2023​

1​

Yes

-3​

Cowboys

40​

0​

Giants

3​

No

Fav

6​

7.70%​

Dog

3​

12.70%​

3​

5.00%​

Cowboys

Yes

Yes

7-6

16-14

AwayFav

Yes

Yes

Yes

Yes

8, 12, 15

1-0-0

100.0%

B

Yes

Yes

Yes

Yes

2023​

1​

No

-2.5​

Bills

16​

22​

Jets

2.5​

Yes

Fav

4​

9.90%​

Dog

22​

-4.50%​

18​

14.40%​

Jets

Yes

4-5

18-8

HomeDog

Yes

Yes

Yes

No

2023​

2​

Push

6​

Vikings

28​

34​

Eagles

-6​

Push

Dog

28​

-55.10%​

Fav

1​

78.40%​

27​

133.50%​

Vikings

Yes

5-4

16-11

AwayDog

Push

Yes

Yes

No

2

1-5-2

16.7%

S

Yes

Yes

2023​

2​

No

-1​

Packers

24​

25​

Falcons

1​

Yes

Fav

6​

40.60%​

Dog

10​

27.80%​

4​

12.80%​

Falcons

Yes

Yes

7-8

15-12

HomeDog

Yes

Yes

Yes

No

1, 6, 7, 8

5-3-1

62.5%

B

Yes

Yes

Yes

Yes

Yes

2023​

2​

No

9.5​

Raiders

10​

38​

Bills

-9.5​

Yes

Dog

3​

60.10%​

Fav

29​

-56.80%​

26​

116.90%​

Bills

Yes

9-10

19-14

HomeFav

Yes

Yes

No

No

2023​

2​

Yes

3.5​

Ravens

27​

24​

Bengals

-3.5​

No

Dog

14​

8.20%​

Fav

17​

-0.90%​

3​

9.10%​

Bengals

Yes

9-3

15-15

HomeFav

No

Yes

Yes

Yes

11, 14

3-9-0

25.0%

S

Yes

Yes

Yes

2023​

2​

Yes

5.5​

Seahawks

37​

31​

Lions

-5.5​

No

Dog

26​

-37.60%​

Fav

2​

69.20%​

24​

106.80%​

Seahawks

Yes

6-4

17-12

AwayDog

Yes

No

Yes

No

2

1-5-2

16.7%

S

Yes

Yes

2023​

2​

No

-3​

Chargers

24​

27​

Titans

3​

Yes

Fav

25​

-36.00%​

Dog

24​

-30.80%​

1​

5.20%​

Chargers

Yes

5-5

12-16

AwayFav

No

Yes

No

No

2023​

2​

No

2.5​

Bears

17​

27​

Buccaneers

-2.5​

Yes

Dog

27​

-41.50%​

Fav

5​

54.10%​

22​

95.60%​

Bears

Yes

9-11

11-16

AwayDog

No

Yes

No

No

2023​

2​

Yes

-3.5​

Chiefs

17​

9​

Jaguars

3.5​

No

Fav

31​

-70.20%​

Dog

12​

16.90%​

19​

87.10%​

Chiefs

Yes

6-4

19-14

AwayFav

Yes

Yes

No

No

2023​

2​

Yes

-1​

Colts

31​

20​

Texans

1​

No

Fav

21​

-17.80%​

Dog

19​

-0.90%​

2​

16.90%​

Colts

Yes

Yes

6-7

15-12

AwayFav

Yes

Yes

Yes

Yes

12

5-1-0

83.3%

B

Yes

Yes

2023​

2​

No

-8​

49ers

30​

23​

Rams

8​

Yes

Fav

11​

21.10%​

Dog

7​

36.60%​

4​

15.50%​

49ers

Yes

Yes

12-9

16-12

AwayFav

No

Yes

Yes

Yes

8

4-4-0

50.0%

B

Yes

Yes

2023​

2​

No

-5.5​

Giants

31​

28​

Cardinals

5.5​

Yes

Fav

18​

-4.10%​

Dog

20​

-15.40%​

2​

11.30%​

Cardinals

Yes

Yes

9-8

15-16

HomeDog

Yes

Yes

Yes

No

1, 6, 7

15-4-1

78.9%

B

Yes

Yes

Yes

Yes

2023​

2​

No

9.5​

Jets

10​

30​

Cowboys

-9.5​

Yes

Dog

4​

55.90%​

Fav

15​

3.10%​

11​

52.80%​

Cowboys

Yes

7-6

10-18

HomeFav

Yes

Yes

No

No

2023​

2​

Yes

3.5​

Commanders

35​

33​

Broncos

-3.5​

No

Dog

13​

14.50%​

Fav

30​

-61.10%​

17​

75.60%​

Broncos

Yes

8-10

15-13

HomeFav

No

Yes

No

No

2023​

2​

Yes

-3​

Dolphins

24​

17​

Patriots

3​

No

Fav

8​

35.00%​

Dog

32​

-79.40%​

24​

114.40%​

Patriots

Yes

6-16

8-21

HomeDog

No

Yes

Yes

No

2023​

2​

Push

-3​

Saints

20​

17​

Panthers

3​

Push

Fav

9​

29.80%​

Dog

23​

-28.80%​

14​

58.60%​

Panthers

Yes

8-9

13-12

HomeDog

Push

Yes

Yes

No

2023​

2​

No

-2.5​

Browns

22​

26​

Steelers

2.5​

Yes

Fav

16​

0.00%​

Dog

22​

-22.10%​

6​

22.10%​

Steelers

Yes

Yes

7-3

14-16

HomeDog

Yes

Yes

Yes

Yes

1

10-4-0

71.4%

B

Yes

Yes

 

[arun_eagertolearn]

Please check this

The logic applied is (please correct if it is wrong)

When Trend = ULK Cover, it is a W, otherwise, it is L

Logic built for P (Push) - when ULK Cover is Push, irrespective of whatever Trends are updated, the result is P

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Week	Ulk Cover	Pick	1	2	3	4
2	1								Trend#	Week	to	Week
3	2								1	1		3
4	1
5	2									W	L	P	Pct
6	1	No	Yes	Yes	No					3	1	0	75%
7	2	No
8	3	No
9	3	No
10	3	Push
11	3	Yes	Yes	Yes	Yes
12	1	Yes	Yes	Yes	Yes
13	2	Yes	Yes	Yes	Yes
14	3	Yes
15	3	No
16	3	No
Sheet3

Cell Formulas
Range	Formula
J6	J6	=LET(inp,CHOOSE(I3,D2:D16,E2:E16,F2:F16,G2:G16),(SUMPRODUCT(--(inp<>0),--(B2:B16=inp),--(A2:A16>=J3),--(A2:A16<=L3))))
K6	K6	=LET(inp,CHOOSE(I3,D2:D16,E2:E16,F2:F16,G2:G16),(SUMPRODUCT(--(inp<>0),--(B2:B16<>inp),--(A2:A16>=J3),--(A2:A16<=L3))))
L6	L6	=LET(inp,CHOOSE(I3,D2:D16,E2:E16,F2:F16,G2:G16),(SUMPRODUCT(--(inp<>0),--(B2:B16="push"),--(A2:A16>=J3),--(A2:A16<=L3))))
M6	M6	=J6/SUM(J6:L6)

 

[Cubist]

Try:

Book1
	BC	BD	BE	BF
1	Trend#	Week	to	Week
2	1	1		2
3		W	L	P
4		3	1	0
Sheet7

Cell Formulas
Range	Formula
BD4	BD4	=SUM(($B$2:$B$33>=$BD$2)*($B$2:$B$33<=$BF$2)*($AH$1:$BA$1=$BC$2)*($AH$2:$BA$33="Yes")*($Y$2:$Y$33="Yes"))
BE4	BE4	=SUM(($B$2:$B$33>=$BD$2)*($B$2:$B$33<=$BF$2)*($AH$1:$BA$1=$BC$2)*($AH$2:$BA$33="Yes")*($Y$2:$Y$33="No"))
BF4	BF4	=SUM(($B$2:$B$33>=$BD$2)*($B$2:$B$33<=$BF$2)*($AH$1:$BA$1=$BC$2)*($AH$2:$BA$33="Yes")*($Y$2:$Y$33="Push"))