What Formula to use for Sum of Hours within a Specific Date Range?

I want to sum up hours in my hours column to a specific cell, but I only want hours within a specific date range. What would be the best way to go about this?

Best Answer


  • That worked! Thank you so much!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Just a quick note or two:

    Repeating the Hours range to say greater than zero is just extra typing as anything that equals zero won't affect your count anyway and it is doubtful that you would have negative hours.

    =SUMIFS(Hours:Hours, Hours:Hours, >0, Date:Date, >=DATE(2020, 1, 1), Date:Date, <=DATE(2020, 10, 30))

    =SUMIFS(Hours:Hours, Date:Date, >=DATE(2020, 1, 1), Date:Date, <=DATE(2020, 10, 30))

    You can also combine criteria for the same range using an AND statement. It doesn't make a huge difference in this case, but if you have a lot of different range/criteria sets I find it is helpful in keeping things organized as I write everything out. That may help out with other formulas that are a little more messy.

    =SUMIFS(Hours:Hours, Date:Date, AND(@cell >=DATE(2020, 1, 1), @cell <=DATE(2020, 10, 30)))

    And finally... There are quite a number of different ways you can structure your date criteria depending on your needs that could potentially make things a little more easy to manage. For example, if you wanted to SUM for everything in June of 2020:

    =SUMIFS(Hours:Hours, Date:Date, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2020))

    This allows you to only worry about month and year numbers and not trying to remember if there are 28, 29, 30, or 31 days in that particular month.

    You can also use cell references if your date range that you want to sum in is established elsewhere on the sheet

    Just a few tips for if you decide/need to expand that may help you keep things organized and easier to manage.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!