Help w/ a Formula - doesn't seem to be excluding 'informational only' items
Hello All,
I am hoping someone can advise if there is a problem with my formula and how to remedy it?
I am stumped and could use some perspective from someone with more knowledge :)
It doesn't appear to be excluding 'informational only' items -
Here is the formula:
=IF(COUNTIFS({Remediations Category}, "Other-Fac-Bldg", {Remediations Core OPCO}, "NSP", {Remediations Year}, "2021", {Remediations Status}, <>"Complete", {Remediations Status}, <>"Cancelled", {Remediations Status}, <>"Informational Only") = 0, "", COUNTIFS({Remediations Category}, "Other-Fac-Bldg", {Remediations Core OPCO}, "NSP", {Remediations Year}, "2021", {Remediations Status}, <>"Complete", {Remediations Status}, <>"Cancelled", {Remediations Status}, <>"Informational Only"))
Best Answer
-
Hey @PKane
If data is not being excluded, then verify that the text between the quotes is an exact match (it's case sensitive) to your dropdown value. You can test if it's excluding the data by removing that criteria from the COUNTIFS to see if the result varies. You may want to try to copy paste directly from your dropdown list.
As a note- numbers should not be enclosed in quotes as it may make smartsheet treat the numbers as text. Remove the quotes from your year numbers.
Does verification of the text "Informational Only" get you any closer?
Kelly
Answers
-
Hey @PKane
If data is not being excluded, then verify that the text between the quotes is an exact match (it's case sensitive) to your dropdown value. You can test if it's excluding the data by removing that criteria from the COUNTIFS to see if the result varies. You may want to try to copy paste directly from your dropdown list.
As a note- numbers should not be enclosed in quotes as it may make smartsheet treat the numbers as text. Remove the quotes from your year numbers.
Does verification of the text "Informational Only" get you any closer?
Kelly
-
Hi @Kelly Moore - as soon as I removed the quotes from the year it popped. My sanity cannot thank you enough for your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!