Count number of instances discounting weekends and Bank Holidays

[johnbird1988]

Hello,

I have this formula to work out the number of instances of "S" appearing within a range. The below formula work brilliantly, however, I cannot get it to incorporate Bank Holidays. I have all the bank holidays listed in a named range called "Holidays". Can any one help with editing the below formula. I have used a previous post to help with the first part.

http://www.mrexcel.com/forum/excel-questions/438745-count-number-instances-discounting-weekends.html

{=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($J10:$NK10,{"S"},0))+ISNUMBER(MATCH(IF(ISNUMBER(MATCH($J$9:$NK$9,{"Sat","Sun"},0)),$J$6:$NK$6,"#"),IF(ISNUMBER(MATCH($J10:$NK10,{"S"},0)),IF($J$9:$NK$9="Fri",$J$6:$NK$6+1,IF($J$9:$NK$9="Mon",$J$6:$NK$6-1))),0)),COLUMN($J10:$NK10)),IF(ISNA(MATCH($J10:$NK10,{"S"},0))*ISNA(MATCH($J$9:$NK$9,{"Sat","Sun"},0)),COLUMN($J10:$NK10))),1))}

Thank you for your help

John

 

[skywriter]

John isn't this the same as this other post. Aladin is trying to help but you didn't answer his questions.

http://www.mrexcel.com/forum/excel-...ed-values-including-weekends.html#post4066753