One formula to count all ADs on workdays in a month

[Retroshift]

Hi, I am looking for one Excel formula to put in cell A1 that counts all AD values on workdays (not weekends nor holiday 2-01) from the beginning to the end of the month.
The dates (see image) are in row 2, the values in row 3.
I am struggling with this.

 

[shinigamilight]

press ctrl shift enter while entering this
I don't know what you mean by holidays, you need to provide a list for holidays separately, this formula will count the values of AD on workdays

=SUMPRODUCT(--(IF(--(WEEKDAY($B$2:$G$2,2)<>6)*--(WEEKDAY($B$2:$G$2,2)<>7),$B$3:$G$3)="AD"))

 

[Retroshift]

Let's say Mon 2-01 (cell C2) and Tue 3-01 (cell D2) are holidays referenced in the range "A2:A3", how can I exclude those holidays just like you excluded the weekends in the above formula?

 

[Fluff]

How about

=SUMPRODUCT((B3:AF3="AD")*(WEEKDAY(B2:AF2,2)<6)*(ISNA(MATCH(B2:AF2,A2:A3,0))))

 

[Retroshift]

Amazing. It worked. Thanks Fluff and shinigamilight

 

[Fluff]

Glad we could help & thanks for the feedback.