How to use result from a multi-select drop down in COUNTIFS formula

I would like to Count the values from a row that contains 1 value from a Multi-Select and 1 of 2 values from a drop down. I have two formulas for the 2 values because 1 of the drop down values contains a < symbol. Here is what I tried to put together
The first one is not registering any values.
=COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], "PPE", [Safety Glasses]:[Safety Glasses], "No Supply")
The second one is showing values, but does not seem to be calculating correctly
=COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], "PPE", ([Safety Glasses]:[Safety Glasses]), CONTAINS("< 2 Week Supply", @cell))
Best Answer
-
Yes. That is the adjustment I was going to suggest for "PPE" if you also wanted to count rows that have "PPE" along with another selection. That's why I asked about that in particular.
Glad you were able to get it working.
Answers
-
The first value is from a Multi-Select drop down (Does customer supply PPE or Consumables) and could contain one or more of 4 selections (PPE, Chemical, Soap, Paper)
-
In the multi select column, are you wanting to count only rows that ONLY have "PPE" in them, or do you also want to count rows that have "PPE" along with some other selection in it?
-
No the second value would relate to one selection in the Multi-select. I did some more work on this and I think I solved my issue. Here are my revised formulas
For No Supply:
=COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], CONTAINS("PPE", @cell), [Safety Glasses]:[Safety Glasses], "No Supply")
For < 2 Week Supply
=COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], CONTAINS("PPE", @cell), ([Safety Glasses]:[Safety Glasses]), CONTAINS("< 2 Week Supply", @cell))
-
Yes. That is the adjustment I was going to suggest for "PPE" if you also wanted to count rows that have "PPE" along with another selection. That's why I asked about that in particular.
Glad you were able to get it working.
-
Thanks for Looking!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!