SumIfs Date Range

edited 12/09/19 in Smartsheet Basics


I'm looking for help writing a SumIfs formula with one of the criterias being a date range.

I'm creating a Time Off Tracking form  to track how much PTO an employee has taken. The tricky part is that we track time off based on date of hire (not the calendar year) which resets at their hire anniversary. This is different for every employee so I'm trying to create a Sum If formula to calculate if an employee has taken time off within their Time Off Period Start and End Date (which I have created separate columns for.)

For calculating Total Sick Days Taken the formula I'm using is:

 =SUMIFS({Total Days Off Requesting}, {Employee}, Employee1, {Type Requested}, ="Sick")

This formula is working great but now I need to add a criteria if the day(s) off requested fall between their Time Off Period Start/End Date. Is this possible?

The sheets I'm using are:

  • Employee Time Off Tracker: Tracks how much paid time off an employee has based on their employee status, their Time Off Period Start and End Date, how many days off they've taken off.
  • Employee Time Off Request Form (REFERENCE SHEET)  - submitted by employees via web form and includes Employee Name, Time Off Date, Days Requested, Time Off Type (Vacation, PTO etc)