Dropdown Column and Not Counting Selected Items

Andthisjustin

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?

Gia Thinh

Hi,

Try this formula of COUNTM with subtraction the Approved item. Hope that helps.

=COUNTM(Items@row) - COUNTIFS(Items@row, CONTAINS("Approved", @cell))

Gia Thinh

Hi,

Try this formula of COUNTM with subtraction the Approved item. Hope that helps.

=COUNTM(Items@row) - COUNTIFS(Items@row, CONTAINS("Approved", @cell))

Andthisjustin

Thank you @Gia Thinh, I am embarrassed to say I was over complicating it as I was too myopic in what I was attempting to do.

The great thing about the forum is fresh eyes helping.

Thank you, thank you, thank you.

Gia Thinh

Your welcome. I am happy that it helped you.