Count number of dates from range, where dates fall between a particular start and end date pair that include today's date.

I am trying to display a count of particular holiday dates that fall within a specific pair of dates, the pair of dates chosen if they include today's date.

My intent in pseudocode:

StartDate = Closest value in StartDateRange <= TODAY();
EndDate = Closest value in EndDateRange >= TODAY();
For each value in HolidayDateRange, count if value is between StartDate and EndDate;

Here is the link to my sample workbook, and all the following formulas will reference that file.

My initial thinking is this, but I'm receiving a general formula error:

=COUNTIFS($B$2:$B$8,>=XLOOKUP(TODAY(),$C$2:$C$53,$C$2:$C$53,"Error",-1,1),$B$2:$B$8,>=XLOOKUP(TODAY(),$D$2:$D$53,$D$2:$D$53,"Error",1,-1))

the expected result as of 23May24 is "1" for Memorial Day.

I did some troubleshooting on 23May24 of the of the formula and its components, and the most complex two appear to be working as expected:

Cell F5 =XLOOKUP(TODAY(),$C$2:$C$53,$C$2:$C$53,"Error",-1,1) returns 5/15/2024 as expected. 

Cell F7 =XLOOKUP(TODAY(),$D$2:$D$53,$D$2:$D$53,"Error",1,-1) returns 5/28/2024 as expected. 

However, when I attempt to plug them in as conditionals for COUNTIF or COUNTIFS, I start seeing errors:

Cell F10 =COUNTIF($B$2:$B$8,>=XLOOKUP(TODAY(),$C$2:$C$53,$C$2:$C$53,"Error",-1,1)) returns an error. 

Cell F12 =COUNTIF($B$2:$B$8,<=XLOOKUP(TODAY(),$D$2:$D$53,$D$2:$D$53,"Error",1,-1)) returns an error. 

I'm suspicious it has something to do with the logical processors not being handled correctly in the formula, so I tried several things to see if that fixed it, with no luck:

Cell F15 =COUNTIF($B$2:$B$8,">=XLOOKUP(TODAY(),$C$2:$C$53,$C$2:$C$53,"Error",-1,1)")

encapsulating the whole conditional in quotes, returns an error.

Cell F17 =COUNTIF($B$2:$B$8,>="XLOOKUP(TODAY(),$C$2:$C$53,$C$2:$C$53,"Error",-1,1)")

encapsulating only the retrieved date value of the conditional in quotes, returns an error.

I'm obviously doing something wrong, but I'm not sure how to proceed from here. Any help in figuring out how to make this behave would be greatly appreciated, as would suggestions on making the whole thing simpler, if possible.

Thanks in advance,

- SilverbackPrime