Excel calculation

[Rehanparkar]

In the "Yearly" named sheet under column E (leaves taken), i want to add the number in column D of Jan to Dec

doesnt want to use sum option..help please

[#2]

@Rehanparkar 

You can use the 3 D reference.

Create a 3-D reference to the same cell range on multiple worksheets - Microsoft Support

[#3]

I did the 1st "Create a 3-D reference" part from the given link but not understanding how to apply the description provided in "Create a name for a 3-D reference"
Also note that the Row & Column for a employee is not the same from Jan to Dec

[#4]

@Rehanparkar 

This is a 365 solution where the position of the names on a given sheet does not matter.  The formula stacks the data from the 12 months and does some filtering to arrive at the totals for each person.

=LET(
    stack, VSTACK(AllMonths),
    employee, TAKE(stack, , 1),
    Leave, TAKE(stack, , -1),
    TotalLeave, LAMBDA(row, LET(filtered, FILTER(Leave, employee = row, 0), SUM(filtered))),
    BYROW(Employees, TotalLeave)
)

[#5]

@Rehanparkar 

If the row and column for an employee isn't the same in all sheets you can't use 3 D reference. An alternative could be VLOOKUP for all tables. An example is in the attached file. If you have access to Office 365 or Excel for the web i'd suggest to use @Patrick2788 's solution.