Counting workday formula - help

In column G in the attached worksheet I have a formula that counts the workdays each month. On a separate sheet is a list of ll holidays and it attempts to exclude them. It does manage to put an "H" when there is a holiday, but it still counts that as a workday so that the following day is one number higher than it should be.

I thought this same formula was working before - and then I removed the weekends (which creates part of the formula moot, but I wouldn't think would mess up the counting so as to count holidays. Can anyone figure out what is going wrong here? Thank you so much!


ADD: Here's the formula if it helps to see it here: =IF(COUNTIF(HOLIDAYS!$C$4:$C$14,A5),"H",IF(OR(F5="Sat",F5="Sun")," ",SUMPRODUCT(--(WEEKDAY(A5+1-ROW(INDIRECT("1:"&DAY(A5))),2)<6),--ISNA(MATCH(A5+1-ROW(INDIRECT("1:"&DAY(A5))),HOLIDAYS!$C$4:HOLIDAYS!$C$14,0)))))

I'm not sure what you're saying...do you want, for instance, $G$5 to = 1 instead of 2 because $G$4 is set to "H"? Thanks!

Perhaps:

Please Login or Register  to view this content.

The above simply validates if the date is a weekday, if it is whether or not it is a public holiday, if it is not it then calculates how many working days have occurred to that point in the month (inclusive)

The use of INDEX with Holidays Range is used to identify the appropriate column of your public holiday range based on year of date.

If you can, try to avoid using SUMPRODUCT in large quantity esp. where Volatile (as was the case here c/o INDIRECT)

edit:

If preferred you could replace the first NETWORKDAYS call (with INDEX) with a standard COUNTIF... whichever seems more intuitive to you:

Please Login or Register  to view this content.

You should find the prior suggestion does as requested (the formula can be applied to the calculation range in it's entirety without need for modification)