Need help correct a complex formula


So, I am working with this formula, which works great, but I need to throw in a CONTAINS aspect into it...and what I am trying is not working.

Current formula (that works):

=AVERAGEIF([Project/Task/Issue/Risk Name]:[Project/Task/Issue/Risk Name], ="Stage 2", [Actual Duration]:[Actual Duration])

How would you add in the condition that it also needs to include "NTI" in the [Project]:[Project] column?

@Paul Newcome tagging you as always :)

Kelly L. Gabel


Tech-Savvy Academy


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need to switch over to an AVG/COLLECT combo to be able to include multiple range/criteria sets.

    =AVG(COLLECT([Actual Duration]:[Actual Duration], [Project/Task/Issue/Risk Name]:[Project/Task/Issue/Risk Name], ="Stage 2", Project:Project, CONTAINS("NTI", @cell)))

    NOTE: It will follow the syntax of the COLLECT function where the range you want to average comes first (more like a SUMIFS vs a SUMIF).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!