Return value of average weekdays based on month criteria

[spurs50]

I have dates for last 2 years in column A including weekends. I want to average all of May-2022 weekdays only for values in column B. I input May-2022 as date format in C3 to return avg of May.
I then want to have all months showing with links like in C3. I have the following two formulas which get very close but don't quite work.

=AVERAGE(IF(WEEKDAY(Sheet1!A2:A500,2)={1,2,3,4,5},Sheet1!B2:B500))
=AVERAGEIFS(Sheet1!B2:B500,Sheet1!A2:A500,">="&Sheet1!C3,Sheet1!A2:A500,"<="&EOMONTH(Sheet1!IC3,0))

Any idea to get it to work?
Thanks

 

[Fluff]

How about

=AVERAGE(FILTER(B2:B500,(WEEKDAY(A2:A500,2)<6)*(A2:A500>=C3)*(A2:A500<=EOMONTH(C3,0))))

 

[KRice]

Here is another idea:

MrExcel_20220728.xlsx
	A	B	C	D
1	Date	Values
2	4/16/2022	20	Query Mo-Yr	Avg Wkdays
3	4/19/2022	23	May-22	19.57143
4	4/22/2022	18
5	4/25/2022	14
6	4/28/2022	29
7	5/1/2022	16
8	5/4/2022	12
9	5/7/2022	28
10	5/10/2022	25
11	5/13/2022	13
12	5/16/2022	22
13	5/19/2022	25
14	5/22/2022	10
15	5/25/2022	26
16	5/28/2022	10
17	5/31/2022	14
18	6/3/2022	25
19	6/6/2022	24
20	6/9/2022	28
21	6/12/2022	19
22	6/15/2022	26
23	6/18/2022	12
Sheet2

Cell Formulas
Range	Formula
D3	D3	=AVERAGE(IF((WEEKDAY(A2:A23,2)<6)*(EOMONTH(+A2:A23,0)=EOMONTH(C3,0)),B2:B23,""))

 

[spurs50]

That works! Thanks so much for your prompt replies. Would have taken me days to figure that out!

 

[Fluff]

Glad we could help & thanks for the feedback.