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!

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


Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Sam Lugiano

    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!

  • Sam Lugiano
    Sam Lugiano ✭✭✭✭✭

    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.

  • Sam Lugiano
    Sam Lugiano ✭✭✭✭✭

    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!