Need help with 2 formulas and counting

Emily McNeeley

Need help building the following:

1. Need to count the number of lines with a specific type where it's a multiselect. So =COUNTIFS({Master Contract List Template Contract Type}, [Contract Information]@row, {Master Contract List Template Status}, "Review & Redline") but contract type is a multi-select so need a "Has"?
2. Need the following values (Request, Review & Redline, Out for Signature, Document Management) in one column to display as "Pending" in another

Jeff Reisman

https://community.smartsheet.com/discussion/86761/need-help-with-2-formulas-and-counting

When you want to know if a multi-select column cell contains a specific text string (among other text in the cell,) you'll want to use the CONTAINS function. For example, if looking for "Monday" in a multi-select cell showing both "Monday" and "Tuesday", HAS will return false because the entire cell needs to match the criteria, whereas CONTAINS will return true because the cell contains the searched-for string. Try this:

=COUNTIFS({Master Contract List Template Contract Type}, [Contract Information]@row, {Master Contract List Template Status}, CONTAINS("Review & Redline", @cell))

For your second question, you can use OR and CONTAINS inside an IF statement to return your value of "Pending" based on the status. Try this in the column you want to show "Pending" in.

=IF(OR(CONTAINS("Request", Status@row), CONTAINS("Review & Redline", Status@row), CONTAINS("Out for Signature", Status@row), CONTAINS("Document Management", Status@row), "Pending", "")

Jeff Reisman

In that case, you just need to wrap an IF around your current formula, one that excludes Vendor Parent Count = 0:

=IF([Vendor Parent Count]@row = 0, "", IF(OR(CONTAINS("Request", Status@row), CONTAINS("Review & Redline", Status@row), CONTAINS("Out for Signature", Status@row), CONTAINS("Document Management", Status@row), "Pending", "Executed"))

If Vendor Parent Count on this row = 0, leave the cell blank, otherwise run this other formula.

Jeff Reisman

https://community.smartsheet.com/discussion/86761/need-help-with-2-formulas-and-counting

When you want to know if a multi-select column cell contains a specific text string (among other text in the cell,) you'll want to use the CONTAINS function. For example, if looking for "Monday" in a multi-select cell showing both "Monday" and "Tuesday", HAS will return false because the entire cell needs to match the criteria, whereas CONTAINS will return true because the cell contains the searched-for string. Try this:

=COUNTIFS({Master Contract List Template Contract Type}, [Contract Information]@row, {Master Contract List Template Status}, CONTAINS("Review & Redline", @cell))

For your second question, you can use OR and CONTAINS inside an IF statement to return your value of "Pending" based on the status. Try this in the column you want to show "Pending" in.

=IF(OR(CONTAINS("Request", Status@row), CONTAINS("Review & Redline", Status@row), CONTAINS("Out for Signature", Status@row), CONTAINS("Document Management", Status@row), "Pending", "")

Emily McNeeley

Thanks @Jeff Reisman - this helped!

Emily McNeeley

@Jeff Reisman Quick question - I want to update the formula =IF(OR(CONTAINS("Request", Status@row), CONTAINS("Review & Redline", Status@row), CONTAINS("Out for Signature", Status@row), CONTAINS("Document Management", Status@row), "Pending", "Executed") to not count parent rows. I have a column called Vendor Parent Count and is running an ancestor formula. So anywhere that the Vendor Parent Count is 0, should be blank

Jeff Reisman

In that case, you just need to wrap an IF around your current formula, one that excludes Vendor Parent Count = 0:

=IF([Vendor Parent Count]@row = 0, "", IF(OR(CONTAINS("Request", Status@row), CONTAINS("Review & Redline", Status@row), CONTAINS("Out for Signature", Status@row), CONTAINS("Document Management", Status@row), "Pending", "Executed"))

If Vendor Parent Count on this row = 0, leave the cell blank, otherwise run this other formula.

Emily McNeeley

I was so close - thanks!!!