SUM "Duration" based on date Range

Joel Brumback

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.

Paul Newcome

Try using something like this...

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

Paul Newcome

Yes except that you will remove the first range which is the range to sum in the SUMIFS.

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

Paul Newcome

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))))

Paul Newcome

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.

Paul Newcome

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)

Paul Newcome

Try using something like this...

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

Joel Brumback

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

Paul Newcome

Yes except that you will remove the first range which is the range to sum in the SUMIFS.

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

Joel Brumback

Okay, both worked fine. thanks

Paul Newcome

Happy to help! 👍️

Joel Brumback

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

Paul Newcome

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))))

Joel Brumback

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

Paul Newcome

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, ............................)

Joel Brumback

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".

Paul Newcome

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.

Joel Brumback

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.

Joel Brumback

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"))

Paul Newcome

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?

Joel Brumback