Help w/ a Formula - doesn't seem to be excluding 'informational only' items

PKane

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"))

Kelly Moore

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

Kelly Moore

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

PKane

Hi @Kelly Moore - as soon as I removed the quotes from the year it popped. My sanity cannot thank you enough for your help!