Average response time in a specific month by project type

John R.

I'm a beginner user trying to get an advanced result and I could use some assistance. I have columns for Project Type, Start Date, and Response Time (among others incidental to this issue). I need a formula to return the average response time for all type 1 projects started in April. I seem to have worked my way into a quagmire with my formula to the point where I'm not even sure what I'm trying to ask the sheet to do. Any ideas would be much appreciated. Thanks.

Paul Newcome

You would need something along the lines of this (for April 2022)...

=AVG(COLLECT({Source Sheet Response Time Column}, {Source Sheet Project Type Column}, @cell = "Type 1", {Source Sheet Start Date Column}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2022)))

John R.

It occurs to me that I should probably mention that the data is on a different sheet from the formula. Thanks.

Paul Newcome

You would need something along the lines of this (for April 2022)...

=AVG(COLLECT({Source Sheet Response Time Column}, {Source Sheet Project Type Column}, @cell = "Type 1", {Source Sheet Start Date Column}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2022)))

John R.

That works perfectly. Thank you so much for the help.

Paul Newcome

Happy to help. 👍️