AVG COLLECT formula with two sets of criteria (Week 52 in two years)

Options

I'm trying to get an average of time spent in each week reviewing documentation. The problem with counting it each week is that week 52 is divided between the years of 2022 and 2023. I want to to collect the time for week 52 when it is in December 2022 and January 2023. Here is what I have so far. I've tried multiple forms of the formula to no avail. This is the closest I have gotten. I don't get an error message, but it is returning nothing (a blank). When I take out the IFERROR and "" at the end, I get #INVALID DATA TYPE.

=IFERROR(AVG(COLLECT({IPC Review TAT Review}, {IPC Review TAT Week}, Week@row, {IPC Review TAT Year}, ="2022", {IPC Review TAT Month}, ="12", AND({IPC Review TAT Review}, {IPC Review TAT Year}, ="2023", {IPC Review TAT Week}, Week@row, {IPC Review TAT Month}, ="1"))), "")

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!