combining network days and a countif/sumif rule in same formula?

[Piers]

Morning all,

not sure if this can be done but I am trying to combine a value i get from a network days showing the number of days an individual was meant to attend during the month until they were discharged.

The query comes around when i would like it to count the number of "A" values based off this value amongst the table but only up until they are discharged and no further.

the current formula i have is:

=IF((NETWORKDAYS.INTL(IF(AJ5<=$B$24,$B$24,AJ5),IF(AK5<=$V$24,AK5,$V$24),AI5,'[CLass_audit - Master.xlsb.xlsm]Master'!$H$2:$H$1000))>0,SUMPRODUCT($B$24:$V$24<=AK5,B27:V27="A"))

where AJ is the admittance date, ak is the discharge date. AI is the network date code 01110011 for 3x per week they are meant to be attneding. and h2-h1000 are the days which are disregarded for leave so no one is to attend then

so the underlined finds the amount of sessions the individual is able to attend within the time period and this works just the next step which i want to know how many of those days did they actually attend from a table of attendance

 

[Piers]

=IF((NETWORKDAYS.INTL(IF(AJ5<=$B$24,$B$24,AJ5),IF(AK5<=$V$24,AK5,$V$24),AI5,'[CLass_audit - Master.xlsb.xlsm]Master'!$H$2:$H$1000))>0,COUNTIFS($B$24:$V$24,"<="&AK5,B27:V27,"A")) got what i wanted