AVERAGEIF with Multiple Conditions

JKP0009
JKP0009 ✭✭
edited 03/09/23 in Formulas and Functions

Hey Smartsheet Community!

I'm trying to get an average processing time for our automation jobs in Smartsheet so we can see how long it is taking for our automated processes to close out a ticket. The formula I'm using is below:

Where {Created} is the year in which the ticket was created, {Crew Ops Support Team Member} is one of the individuals on my team (including our group mailbox for automated items, and {Average Process Time} capturing the number of days between creation and closure.

=AVERAGEIF(AND(({Created}, IFERROR(YEAR(@cell), 0) = 2023),({Crew Ops Support Team Member}, "crewtech@domain.com")), {Average Process Time})


I get an UPARSEABLE error when I run this. Can someone help me figure out what I'm doing wrong?

Thanks!

Answers

  • JKP0009
    JKP0009 ✭✭

    Hey all!! I was able to solve my own question, but I'll post how below for anyone else who may come along looking for the same answer! (In the interim, the request actually changed to search for specific users to find the average processing time.)

    =AVG(COLLECT({Average Process Time}, {Created}, IFERROR(YEAR(@cell), 0) = 2023, {Crew Ops Support Team Member}, "John Smith"), COLLECT({Average Process Time}, {Created}, IFERROR(YEAR(@cell), 0) = 2023, {Crew Ops Support Team Member}, "Joseph Pierce"), COLLECT({Average Process Time}, {Created}, IFERROR(YEAR(@cell), 0) = 2023, {Crew Ops Support Team Member}, "Sarah Johnson"))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 03/10/23

    Hi @JKP0009

    I hope you're well and safe!

    Excellent! Glad you got it working!

    Thanks for sharing!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!