Count & AVG only if another column contains "text"

02/01/18 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

  • L@123[email protected] ✭✭✭✭✭
    edited 02/02/18

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

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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

Sign In or Register to comment.