# SUM "Duration" based on date Range

I have a sheet with an "End Date" and "Duration" columns. I need a formula that will look at the "End Date" and see if it is equal to or between two dates and then SUM the duration. Ex. Date range 6/8/2020 through 6/14/2020 and I want to SUM the total duration. Thank you in advance.

Tags:

• ✭✭✭✭✭✭

You would use an AVG/COLLECT with the same syntax as the SUMIFS and one extra closing parenthesis on the end like so...

=AVG(COLLECT(Duration:Duration, [End Date]:[End Date], AND(@cell >= DATE(2020, 06, 08), @cell <= DATE(2020, 06, 14))))

• ✭✭✭✭✭✭

Yes. You would need to add in the range/criteria set in the COLLECT function.

=AVG(COLLECT(Duration:Duration, [End Date]:[End Date], AND(@cell >= DATE(2020, 06, 08), @cell <= DATE(2020, 06, 14)), [Assigned To]:[Assigned To], "AARON"))

You can use this pattern of range, criteria, range, criteria within the COLLECT function as many times as needed to cover different variables that you want to incorporate so long as you do not exceed 4,000 characters (including spaces).

So if you wanted the above to also look at tasks that haven't yet been completed...

=AVG(COLLECT(Duration:Duration, [End Date]:[End Date], AND(@cell >= DATE(2020, 06, 08), @cell <= DATE(2020, 06, 14)), [Assigned To]:[Assigned To], "AARON", Status:Status, @cell <> "Complete"))

So on and so forth.

• ✭✭✭✭✭✭

Not at all. You can't know something until you learn it.

Try this...

=IFERROR(original_formula, 0)

=IFERROR(AVG(COLLECT({Drop Bury Tracker Range 7}, {Drop Bury Tracker Range 6}, AND(@cell >= DATE(2020, 5, 18), @cell <= DATE(2020, 5, 24)), {Drop Bury Tracker Range 2}, "RICH BONK", {Drop Bury Tracker Range 4}, @cell <> "SCHEDULED")), 0)

«1

• edited 06/16/20

Thanks Paul, would the same formula work for COUNTIFS if I wanted to count the number of instances?

• Okay, both worked fine. thanks

• ✭✭✭✭✭✭

Happy to help! 👍️

• Last question, what would I use to average the "Duration" for that date range?.

• ✭✭✭✭✭✭

You would use an AVG/COLLECT with the same syntax as the SUMIFS and one extra closing parenthesis on the end like so...

=AVG(COLLECT(Duration:Duration, [End Date]:[End Date], AND(@cell >= DATE(2020, 06, 08), @cell <= DATE(2020, 06, 14))))

• Okay, I was unfamiliar with "COLLECT". First I have heard of it.😀

• ✭✭✭✭✭✭

It is like adding an "IFS" to a function.

COLLECT(range to collect for original function, first criteria range, first criteria, second criteria range, second criteria, ............................)

• I have another column "Assigned To" in this same sheet. Can I use the same AVG(COLLECT formula and only look for a certain name? So I want to Average the "Duration" if the "End Date" is between two dates and it is "Assigned To" "AARON".

• ✭✭✭✭✭✭

Yes. You would need to add in the range/criteria set in the COLLECT function.

=AVG(COLLECT(Duration:Duration, [End Date]:[End Date], AND(@cell >= DATE(2020, 06, 08), @cell <= DATE(2020, 06, 14)), [Assigned To]:[Assigned To], "AARON"))

You can use this pattern of range, criteria, range, criteria within the COLLECT function as many times as needed to cover different variables that you want to incorporate so long as you do not exceed 4,000 characters (including spaces).

So if you wanted the above to also look at tasks that haven't yet been completed...

=AVG(COLLECT(Duration:Duration, [End Date]:[End Date], AND(@cell >= DATE(2020, 06, 08), @cell <= DATE(2020, 06, 14)), [Assigned To]:[Assigned To], "AARON", Status:Status, @cell <> "Complete"))

So on and so forth.

• edited 06/18/20

Thanks, I wasn't even close. I didn't have the two closed parentheses at the end of the date and tried to add it all in one string.

• I have one entry in this date range and the result says "#DIVIDE BY ZERO". What would be causing that with the below formula.

=AVG(COLLECT({Drop Bury Tracker Range 7}, {Drop Bury Tracker Range 6}, AND(@cell >= DATE(2019, 12, 30), @cell <= DATE(2020, 1, 5)), {Drop Bury Tracker Range 2}, "RICH BONK", {Drop Bury Tracker Range 4}, @cell <> "Complete"))

• ✭✭✭✭✭✭

The trick to those two closed parenthesis after the date...

You want to add to the COLLECT function. So you have one closing parenthesis to close the DATE function. Then you have another to close the AND function. Once you close the AND function, you are back to working in the COLLECT function and can enter your comma and type away.

Another tip for working inside of formulas with multiple functions: The helper box below the cell will tell you which function you are working in.

The #DIVIDE BY ZERO error leads me to believe that it is finding no rows that match all of the criteria. Can you show a screenshot of the row that should be grabbed?

• edited 06/18/20

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!