Dropdown Column and Not Counting Selected Items
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
-
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 Technology - Smartsheet Solution Partner.
Answers
-
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 Technology - Smartsheet Solution Partner.
-
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.
-
Your welcome. I am happy that it helped you.
Gia Thinh Technology - Smartsheet Solution Partner.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!