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

Options

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    PKane ✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!