Need help correct a complex formula

Options

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

Owner/Founder

Tech-Savvy Academy

kellylgabel.com

Tags:

Best Answer

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

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!