Count & AVG only if another column contains "text"


Hi Again,

After scouring discussion boards I have managed to Average the total of the rows, see below

=IF((COUNT([Days to respond]194:[Days to respond]214)) = (COUNTIF([Days to respond]194:[Days to respond]214, 0)), 0, SUM([Days to respond]194:[Days to respond]214) / ((COUNT([Days to respond]194:[Days to respond]214))))

But now i would like it to only sum & AVG rows that contain text from another column. Count & AVG only if Team1 contains "URD" 

Can anyone help me create the function?


  • L_123
    L_123 ✭✭✭✭✭✭
    edited 02/02/18

    try the find function. =if(find("URD",@cell)>0,1,0)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I'm not sure why your formula is so complicated. Is there something about the data that would cause you to want to do it that way?

    Try this:

    =IFERROR(AVG(COLLECT([Days to respond]194:[Days to respond]214, [Team1]194:[Team1]214, "URD")), 0)

    This returns 0 when there are no "URD" entries (because AVG() will give an error)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!