Search code examples
excelexcel-formulaoffice365ms-office

Calculating planned working hours based on department in excel worksheet

In the attached file I have tried to subtract columns "out" from "in" to find planned working hours based on the department a person is working in. Sum two columns based on text.xlsx Timesheet planned vs actuals Department VS and Cleaning should be summarized in the fields C22:O22

Trying to find a way to subtract C4:C20 from B4:B20 based on the Department column

=IF(OR($B$4:$B$20="VS";$B$4:$B$20="Cleaning");TEXT((SUM(F4:F20)-SUM(E4:E20));"t");"")

=TEXT(SUM((IF(B4:B20="VS";SUM(D4:D20);0))-(IF(B4:B20="Cleaning";SUM(C4:C20);0)));"t")

I am not sure what I can use to try to fix it

Solution

  • Assuming no version constraints per your tags, you can use the FILTER function:

    =SUM(FILTER(D$4:D$20-C$4:C$20,$B$4:$B$20="VS"))

    Adjust column references as appropriate for your data.