AVG(COLLECT with Multiple Criteria

✭✭✭✭✭

Hi,

I am trying to calculate the Average for the "Days Open" column using multiple criteria. Currently I am using two different formulas (below) to try and come up with one number. It would be great to use one formula instead of two.

1. =AVG(COLLECT({Master Case List Days Open}, {Open/Closed}, "Open", {Date Referred}, <DATE(2022, 1, 1), {Attorney}, "Bob"))
2. =AVG(COLLECT({Master Case List Days Open}, {Master Case List Fee Rec'd/File Close Date}, >DATE(2020, 12, 31), {Master Case List Fee Rec'd/File Close Date}, <DATE(2022, 1, 1), {Attorney}, "Bob"))

• ✭✭✭✭✭✭

The COLLECT function can use many criteria. You're already using three criteria in the first one, why not just add more?

=AVG(COLLECT({Master Case List Days Open}, {Open/Closed}, "Open", {Date Referred}, <DATE(2022, 1, 1), {Master Case List Fee Rec'd/File Close Date}, >DATE(2020, 12, 31), {Master Case List Fee Rec'd/File Close Date}, <DATE(2022, 1, 1), {Attorney}, "Bob"))

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭

I tried that formula but I got a #DIVIDE BY ZERO error message. I then tried adding IFERROR and I got a #INCORRECT ARGUMENT SET message.

What may be happening is that the beginning of my formula is requesting "Open" cases but the end of the formula is asking for "Close Date" which lists as "Closed". I also tried a couple of AND(OR but did not work.

• ✭✭✭✭✭

After a day to think about it, turns out the formula was much simpler than I thought.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!