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?

Find more posts tagged with

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…