using if functions to perform multiple logical evaluations

lori.everman112941

I am needing to look at one cell to see if it has a specific word and then if it does use networkdays to calculate using two other cells.  I am able to do this in excel, but unable to find the correct formula to do in smartsheet.  Below is an example of what I used in creating it in excel.

=IF(ISNUMBER(SEARCH("clarity",$A$2)),IFERROR(NETWORKDAYS(B2,C2),""))

In smart sheet I the column name for (A2) in my excel example is Application.  I need to know how many networdays it is taking from one stage to another.

Mike Wilday

Try one of these 

=IF(CONTAINS("clarity", Application@row)=True, IFERROR(NETWORKDAYS([Date Column Title]@row, [Second Date Column Title]@row),""))

You'll need to replace Date Column Title with your actual date column and the Second date column title with the 2nd title. (You might have to put True in quotes). Unsure. 

=IF(FIND("clarity", Application@row) > 0, IFERROR(NETWORKDAYS([Date Column Title]@row, [Second Date Column Title]@row),""))

Hope this helps! 

Paul Newcome

You actually don't need to specify true when using CONTAINS like this.

 

=IF(CONTAINS(......., .................), IFERROR(.....................................))

 

Will work just fine. When using it this way, you only need to specify if you are searching for a false result in which case I personally use a NOT function instead of using false.

Mike Wilday

@Paul Good to know! I didn't have time to test it out so I thought i'd play it safe.  

Paul Newcome

I use it quite a bit breaking down data from a roll-up into year-to-date metrics.

 

Another neat little observation...

 

Because it is a true/false output, it can be used as a stand-alone in checkbox columns or the flag type symbol column.

.

=IF(CONTAINS("text", [Column Name]@row) = true, 1, 0)

=IF(CONTAINS("text", [Column Name]@row), 1, 0)

=CONTAINS("text", [Column Name]@row)

.

They all function the same exact way when placed in a checkbox.

.

.

CONTAINS is also not case sensitive. The only two case sensitive functions that currently exist are FIND and HAS.