Asking for AVG if specific date criteria is meet.


Asking to average the number of days worked on a project if the due date was after 2020, 12, 31.

Something in this is not working correctly.

=AVG(COLLECT({Days Total}:{Days Total}), IF({Completion Date}, >DATE(2020, 12, 31)))


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/27/21

    Hi @Tbech

    From your use of curly brackets {} I see you have a cross sheet reference. When using cross sheet references, we don't use the colons like we would if we were referencing columns on the same sheet.

    Since you only have a single criteria to evaluate, you have two options of formulas. I'll show you the AVG(COLLECT) first since you started with that (always my preference as well)

    =AVG(COLLECT({Days Total}, {Completion Date}, >DATE(2020, 12, 31), {Completion Date}, ISDATE(@cell)))

    I added the ISDATE criteria to weed out any date errors that sometimes occur.

    Smartsheet does have an AVERAGEIF formula that can be used when you have a single criteria, like you do above.

    AVERAGEIF has the syntax of criteria range, criteria, [Average range (if different than the criteria range)]

    Applying this to your example:

    =AVERAGEIF({Completion Date}, >DATE(2020, 12, 31), {Days Total})



  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    You are missing the Completion Date range and your syntax is not correct. Should be,

    = AVG(COLLECT([Days Total]:[Days Total], [Completion Date]:[Completion Date], DATE(2020,12,31)))

    If your data is in a different sheet then,

    = AVG(COLLECT({Days Total Range}, {Completion Date Range}, DATE(2020,12,31)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!