# Count & AVG only if another column contains "text"

Options
edited 12/09/19

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?

## Comments

• ✭✭✭✭✭✭
edited 02/02/18
Options

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

• ✭✭✭✭✭✭
Options

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)

Craig

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!