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

Victoria Scholly

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?

David Tutwiler

I think SUMIFS is your best shot. You'd say you want to SUM everything in the Hours column that is also has a date within the range you're looking for. Something like:

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

David Tutwiler

I think SUMIFS is your best shot. You'd say you want to SUM everything in the Hours column that is also has a date within the range you're looking for. Something like:

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

Victoria Scholly

That worked! Thank you so much!

David Tutwiler

Great news!

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.