Lookup data based on calendar - Leave Tracker

Hi Team,
Seeking your help as im trying to create a leave planner which the agent to plot their leaves for approval

At the same time a summary for them to view their leave summary based on the leave planner.

I find it complicated but its really will be helpfull so that i can see broader team leaves rather than one team.

* Summary table populates based on input from "Leave Planner" sheet
* AL earned are to be populated base on the current month and joined date
* Annual Leave are earned, which means agent earn 1.33 every month. This to give visibility how many leaves they had earned till current month.
* EL are deducted from from AL earned
* Other leaves are earned and given of the beginning of year
* There are 1 day leave and 0.5 days leave
* only populated for 3 months in the file. Eventually it will be 12 month data.

Administrative Note:

Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

So, for starters, in D5 (which should be 5, not 5.5 as far as I can see):

=SUMPRODUCT(('Leave Planer'!$A$4:$A$39=$A5)*('Leave Planer'!$B$4:$AF$39="AL"))+(SUMPRODUCT(('Leave Planer'!$A$4:$A$39=$A5)*('Leave Planer'!$B$4:$AF$39="AL0.5"))/2)

Are you able to adapt this yourself for the other values required?