Need help with 2 formulas and counting
Need help building the following:
- 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"?
- Need the following values (Request, Review & Redline, Out for Signature, Document Management) in one column to display as "Pending" in another
Best Answers
-
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", "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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", "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks @Jeff Reisman - this helped!
-
@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
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I was so close - thanks!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!