Averageifs or some other way ?

[JimM]

Hi

I need a formula to average some data based on a date range but excluding weekends, a simplified data set would be

Date	Count
16/01/2021​	1​
17/01/2021​	2​
18/01/2021​	3​
19/01/2021​	4​
20/01/2021​	5​
21/01/2021​	6​
22/01/2021​	7​
23/01/2021​	8​
24/01/2021​	9​
25/01/2021​	10​
26/01/2021​	11​

What I want is the average of the count column between the dates 18/01/21 and 25/01/21 but excluding the 23rd and 24th - the date range (ie 18th - 25th) is defined by input into cells F2 & F3 so the formula needs to cover the whole range

I've tried AVERAGEIFS which works for the date range

=AVERAGEIFS(B2:B12,A2:A12,">="&F2,A2:A12,"<="&F3)

but when I tried to exclude dates by incorporating a WEEKDAY element it fails

Any suggestion on how I achieve my goal would be much appreciated

 

[mart37]

=SUMPRODUCT((--(WEEKDAY(A2:A12,2)<6))*(A2:A12>=F2)*(A2:A12<=F3)*(B2:B12))/SUMPRODUCT(--(WEEKDAY(A2:A12,2)<6)*(A2:A12>=F2)*(A2:A12<=F3))

 

[JimM]

Good work mart37 works treat

Thanks