Count & AVG only if another column contains "text"

Emma_w4tevr

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

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

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)

Craig