Total time-off days by year

I am having trouble creating a formula on my Staff Total Days Off sheet that pulls information from my Time-off Request Log sheet but for a specific year.

Sheet 1: Time-off request sheet tracks each employee's approved time-off dates and days with an additional column for YEAR of request.

Sheet 2: The Total Days Off sheet sums up all of the time-off days used by employee pulling data from Sheet 1. I have successfully created the formula that adds all of the time-off days used per staff.

Total Days Used formula: =SUMIF({EMP ID}, [EMP ID]1, {Days Out})

However, it is adding all days off for all of the YEARS (eg: 2022, 2023 etc) for each employee. How do you write a formula that Totals Days Used by employee but only for a specific year eg: 2023?


Thanks,

Sylvia

Tags:

Answers

  • Jaime M.
    Jaime M. ✭✭✭

    Hi Sylvia,

    If you set up Sheet 1 like in the first snip, with a column for time off start and end, you can have a helper column that sums the total days off per request. I used the Networkdays formula, but didn't include a reference list of holidays (which I would suggest you use to make the count accurate, but make in a separate reference sheet) - this is shown in the second snip below.

    =NETWORKDAYS([Time Off Start]@row, [Time Off End]@row)


    Then, in your Sheet 2, you can reference Sheet 1 using the formula in the last snip below.

    =SUMIFS({Count If - Days Off}, {Count If - Employee ID}, [Employee ID]@row, {Count If - Year}, "2023")

    Hope this helps!

  • SylviaL
    SylviaL ✭✭

    Thanks Jamie!

    It didn't work though. I set up the Time off sheet as you said. Then entered the formula in Sheet 2:

    =SUMIFS(COUNTIF({Days Out}, COUNTIF({EMP ID}, [EMP ID]@row, COUNTIF({Year}, "2023"))))

    I got an Incorrect Argument Set on the cell.

    Any ideas on what I did wrong?

  • Erik Jensen
    Erik Jensen ✭✭✭✭

    Hi Sylvia,

    Did you get this figured out? If not, I might be able to help.

  • SylviaL
    SylviaL ✭✭

    Yes I did. Many thanks for reaching out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!