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.
Your help is much appreciated!
- =AVG(COLLECT({Master Case List Days Open}, {Open/Closed}, "Open", {Date Referred}, <DATE(2022, 1, 1), {Attorney}, "Bob"))
- =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"))
Answers
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks for the reply.
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!