Help with countif formula for calendar (countif any date in table present)

Hi,

I was hoping to get a little bit more help on an another (and last) formula for my companys work schedule. I'm trying to calculate which employees have worked on a bunch of specific dates (stat holidays). I have figured out the formulas to calculate the date of each holiday as they will vary from year to year. These dates are in a table on a sheet called LOCKED. I have put the formula in the sheet called STATS in cell F5. I want it to see if any of the dates in the table are present in the sheet called schedule (Schedule!$B$3:$AF$3) for the selected month. If they are count the cell for that date in the row employee 1 (Schedule!$B$5:$AF$6) if it has a 1 or a 2 in that cell signifying if the worked shift 1, shift 2. Otherwise do nothing. I have figured out the formula for only one date in the list at a time and it has to be the same month as the date selected. For example if I have the calendar set for December 2021 (calendar days adjust automatically when the month and year are chosen) and I have the date chosen on the list to December 25, 2021 it works, If I change the month to February I get a #N/A This is what I have so far. =COUNTIF(INDEX(Employee1, 0, MATCH(LOCKED!F5, Month_Numbers, 0)), "1")+COUNTIF(INDEX(Employee1, 0, MATCH(LOCKED!F5, Month_Numbers, 0)), "2"). The problem that I am having is I can't figure out how to create the formula to see if there are any of the dates present in the selected month not just one and regardless of which month is selected. As well as if there are more than one (December has 2 Christmas Day and Boxing Day). I'm not sure if I am on the right track to accomplish this or not but any help would be greatly appreciated. I have attached a file below.

I made the following changes:

Named range "Stat_Hols" for holiday list in column F of "Locked"

Changed named ranges "Employeex" to reference the first line of the (effectively merged) cell reference

in "Stats" F5

=IFERROR(SUMPRODUCT(--(Month_Numbers=Stat_Hols)*(INDIRECT(SUBSTITUTE(A5," ",""))>=1)),"").

OR

in Column G

=IFERROR(SUMPRODUCT(--(Month_Numbers=Stat_Hols)*(INDEX(Schedule!$B$5:$AF$32,MATCH($A5,Schedule!$A$5:$A$32,0),0)>=1)),"")

Removes the need of "Employeex" named ranges

Copy down

I strongly urge you to remove the "merging" of "Employee" and "Telephone" by putting "Telephone" in its own column to avoid unnecessary complications to formulae.

It is "pretty" but not "practical": in fact, bad practise.

Thank you so much for your assistance. I will have to see what my employer wants to do in regards to the phone number cells. It's set up this was so that when it's printed it fits on one page. I do agree with you that it's not the best for the solution. Now that it's done all cells that have formulas will be locked and hidden. The person imputing the scheduling information will only be able to select and input numbers where they need to.

Once again I really do appreciate you taking the time to help me out!

You're welcome.