Dropdown Column and Not Counting Selected Items

Andthisjustin ✭✭
edited 06/25/23 in Formulas and Functions

Hey all,

I have a dropdown cell with 17 items in it. Its a miscellaneous list of items you might find in a store, but if nothing is found to be out of compliance, then we would use the last item in the list which is "Approved". My issue is, I would like to count any of the checked items that might not be in compliance, but not if approved is selected. I am building a points scoring system, any of the things not compliant are a point against the total score, but if its approved I don't want to count it.

In the past I would do this...

=COUNTIF([Register NOT compliant]@row, CONTAINS("Item 1", @cell)) + COUNTIF([Register NOT compliant]@row, CONTAINS("Item 2", @cell))

And so on, counting each item I want to count from the name of the first item to the name of the last. While it works, I was hoping to do a COUNTM with a NOT function to lessen the length of the formula. I have tried the following, but the count is incorrect.

=COUNTM([Register NOT compliant]@row, NOT(HAS(@cell = "Approved"))) or

=COUNTM([Register NOT compliant]@row, NOT(CONTAINS("Approved", @cell)))

When I use the above it gives me a 2 as a count, when Approved is selected. When I select all 17 items in the list (approved is the 17th item), the count should be 16 but its reading 18. It does not matter if it is HAS or CONTAINS here as I have tested both.

Not entirely sure why its doing that. Thoughts?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!