Calculate Balance Hours for a group of people

I need to calculate the balance hours based on the available hours (Fixed) and Allocated hours (will change based on new allocations). I have created the tables and formulas in the attached sheet. Let me know if there is any other way to achieve this.

I am facing an issue in the sheet.

I am trying to pull the below formula to the cells I44,J44,K44,L44

In cell G44 =SUMIFS(G2:G40,D2:D40,"Offshore",E2:E40,"Active")
In cell H44 =SUMIFS(H2:H40,D2:D40,"Offshore",E2:E40,"Active")

I need it to be updated automatically as

In cell I44 =SUMIFS(I2:I40,D2:D40,"Offshore",E2:E40,"Active")

but it is updating as

=SUMIFS(I2:I40,F2:F40,"Offshore",G2:G40,"Active")

can someone provide me some ideas on how to fix this issue


Sum Formula.xlsx

Your problem is that these formulas have all references RELATIVE.

Columns D and E need to be FIXED.

Thus In cell G44 =SUMIFS(G2:G40,$D2:$D40,"Offshore",$E2:$E40,"Active") should solve the problem. Just copy across!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Thanks David, it works fine now.

can you comment on the way i am calculating it right now. is there a better way instead of having two sheets ?