[Solved] Formula Help

[Shellsyt]

Hello
I am trying to set up some formulas to help calculate leave balances for staff.
I will be downloading the pay run hours information into a worksheet, adding if the hours are to be used for calculating the accrual (accruable, not accruable). If the hours are accruable, I want to be able to sum the hours and bring them across to the staff list for each staff member.

I have tried several different combinations, but just have not been able to get the formula to work, so I am hoping that someone can help me out

I have uploaded a sample excel workbook.

Thank you in advance.

 

[Seemanto Saha]

Hello
I am trying to set up some formulas to help calculate leave balances for staff.
I will be downloading the pay run hours information into a worksheet, adding if the hours are to be used for calculating the accrual (accruable, not accruable). If the hours are accruable, I want to be able to sum the hours and bring them across to the staff list for each staff member.

I have tried several different combinations, but just have not been able to get the formula to work, so I am hoping that someone can help me out

I have uploaded a sample excel workbook.

Thank you in advance.

Hello Shellsyt,

Welcome to ExcelDemy and thanks for sharing your problem with us. I understand that you want to sum up the accruable hours for each employee listed in the “Payrun Hours Table” worksheet and bring them to the “Staff List” worksheet.

We can achieve this by using the SUMIFS function. From the “Staff List” worksheet, apply the following formula in Cell I2.​

=SUMIFS(Payrun_Hours_Paid[Hours worked],Payrun_Hours_Paid[Source Name],'Staff List'!A2,Payrun_Hours_Paid[Accrue hours or not],"Accruable")

Here, the SUMIFS function sums up the Hours worked column from the “Payrun Hours Table” sheet based on the following two conditions.​

• Source Name column value has to match the Employee column value.
• Accrue hours or not column value has to be “Accruable”.

After applying the formula in Cell I2, press the Enter key and drag down the Fill Handle icon.

Now you can easily calculate leave balances for staff. The following two articles might be useful for your calculations.​

• Calculate Leave Balance in Excel
• Calculate Accrued Vacation Time in Excel

I hope the above formula was helpful to you. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy