SUMIFS between date range formula not working

Leo Wild

Hi All,

I am trying to sum the total overtime hours cost for various roles on a project and I am breaking this down into weeks, so I need my formula to look at a range between 2 dates. My formula works correctly for some roles but not others.

The OT hours information is collected via a smartsheet form which, amongst other things, requires users to submit their start date, end date and total hours worked.

I have a separate sheet of formulas so I am referencing the main data sheet from here.

My formula is as follows:

=SUMIFS({OT Tracker Range 1}, {OT Tracker Range 4}, "XYZ Role", {OT Tracker Range 2}, >=DATE(2023, 11, 20), {OT Tracker Range 3}, <=DATE(2023, 11, 26))

I think this should show me the sum cost (Range 1) of overtime hours worked by "XYZ Role" (Range 4) between November 20th 2023 (Range 2) and November 26th 2023 (Range 3)

This formula is working for submissions with an actual start date of 20/11/2023 and and an actual end date of anything before 26/11/2023.... but if someone entered a start date of 24/11/2023 for example, the formula does not sum the cost and returns 0 instead.

I thought that by using the ">=DATE" it would look for submissions with a date equal to or greater than 20/11/2023 and equal to or less than 26/11/2023.

Interestingly, if I change the first date to 24/11/2023 to match and actual line of data collected on the form for this particular person, it suddenly returns the sum costs.

Apologies if that's not very clear... but in short, the formula is not looking for entries submitted between the date range that I need it to regardless of the fact I know there have been entries during that time!

Kelly Moore

Hey @Leo Wild

Since your Start Date and End Dates are in different columns, you need to add the min and max dates to each column.

=SUMIFS({OT Tracker Range 1}, {OT Tracker Range 4}, "XYZ Role", {OT Tracker Range 2},AND(@cell>=DATE(2023, 11, 20),@cell<=DATE(2023, 11, 26)), {OT Tracker Range 3}, AND(@cell>=DATE(2023, 11, 20),@cell<=DATE(2023, 11, 26)))

Will this work for you?

Kelly