Calculating number of holidays taken in a selected period

I need to calculate the number of holidays people have taken within a given date range, but I’ve got no idea where to start.

Any help would be appreciated.

>Solution :

Try something along the lines of using :

=LET(
     _Holidays, XLOOKUP(P5,H$5:N$5,H$6:N$14),
     _StartDate, DATE(G$2,E$2,C$2),
     _EndDate, DATE(G$3,E$3,C$3),
     SUM((_Holidays>=_StartDate)*(_Holidays<=_EndDate)))

Using BYROW() one can spill the output using one single formula as well:

=BYROW(P5:P11,LAMBDA(x,
 LET(
     _Holidays, XLOOKUP(x,H5:N5,H6:N14),
     _StartDate, DATE(G2,E2,C2),
     _EndDate, DATE(G3,E3,C3),
     SUM((_Holidays>=_StartDate)*(_Holidays<=_EndDate)))))

Or instead of using XLOOKUP() one can use FILTER() as well:

=BYROW(P5:P11,LAMBDA(x,
 LET(
     _Holidays, FILTER(H6:N14,H5:N5=x),
     _StartDate, DATE(G2,E2,C2),
     _EndDate, DATE(G3,E3,C3),
     SUM((_Holidays>=_StartDate)*(_Holidays<=_EndDate)))))

Basically it is one line of formula, I have tried to break it down into segments to show some clarity:

=LET(x, FILTER(H$6:N$14,H$5:N$5=P5),SUM((x>=DATE(G$2,E$2,C$2))*(x<=DATE(G$3,E$3,C$3))))

Explanation:

• Extract the list of holidays using XLOOKUP() or FILTER()
• Then compare it with the From: and with To:
• Finally do a sum of the products using SUM() function.